Recap challenge #1
Lesson 7 of 13 in Coddy's SQL for advanced course.
Challenge
EasyPictures have all kinds of characteristics, such as the amount of ink (in liters), the number of colors, and the number of people who want to buy them.
Day over day, we should see an increase in the number of people interested in newly created pictures, while the artist should use less ink and less color to save costs. In this challenge, we would like to examine this hypothesis.
Calculate the difference between the current ink_liters and the ink_liters one day before it, and do the same for colors_num. Let's call these columns ink_diff and colors_diff, respectively.
Filter all days where the sum of ink_diff + colors_diff + num_people_interested is greater than 0.
Finally, the more people who are interested, the better the day. Add a column named day_rank, numbered from 1 to the number of days remaining after filtering the table, ordered by the num_people_interested column.
Return the day_of_month and the day_rank order by day_rank in ascending order.
Try it yourself
All lessons in SQL for advanced
4Summary
Final challenge #12Window Functions part 1
ROW_NUMBER functionORDER BY criterionPARTITION BY criterionLEAD & LAG functionsRecap challenge #1Recap challenge #23Window Functions part 2
RANK & DENSE_RANK functionsNTILE functionAggregation functionsROWS & RANGE criterion