Recap - LEAD & LAG
Part of the Fundamentals section of Coddy's SQL journey — lesson 62 of 72.
Challenge
MediumAvailable tables and columns:
<b>air_conditioners</b>:<b>id</b>,<b>efficiency</b>,<b>strength</b>,<b>month</b>
We want to track how air conditioner performance changes over time. Your task is to find air conditioners that had declining performance between months.
Here's what to do:
- For each air conditioner in each month, calculate its performance ratio by dividing the average efficiency by the average strength using
AVG(efficiency)/AVG(strength), grouped byidandmonth - Compare each air conditioner's current month performance with its performance two months later
- Find cases where the current performance is significantly better than future performance (current ratio ÷ future ratio > 0.5)
- Return the air conditioner id, month, and the comparison ratio for these cases
Try it yourself
All lessons in Fundamentals
4More Keywords
The IN keywordThe BETWEEN keywordThe LIKE keywordThe AS keywordRecap - Cellphone Models
2Conditions
Conditions BasicsThe AND keywordThe OR keywordThe NOT keywordMultiple Conditions CombinedParenthesisBooleans5Arithmetic Operations
Mathematical OperatorsMathematical ColumnsThe Modulo OperationThe ROUND() Function8Statistics
Built-In Aggregate Part 1Built-In Aggregate Part 2Grouping Part 1Grouping Part 2Subqueries Part 1Subqueries Part 2Recap - Total Gain ShopRecap - Scooter ShopRecap - Coffee Shop11Window Functions part 1
ROW_NUMBER functionORDER BY criterionPARTITION BY criterionPARTITION & ORDERLEAD & LAG FunctionsRecap - LEAD & LAGRecap - PicturesRecap - Boxes3Specific Return Format
Null valuesSort Results Part 1Sort Results Part 2Recap - Cyber Security FirmLimit number of recordsRecap - Vehicle Factory6Intro Challenges
Recap - Parliamentary ElectionRecap - Police Criminal ArrestRecap - Bar Beverage ContainerRecap - Engineer new columns