HardSQL challenge #288CTEWINDOW FUNCTIONLAGPATTERN

Find the Longest Streak of Rising Daily Revenue

Return the start, end and length of the longest run of order days on which revenue kept increasing.

The challenge runs on the SQLabHub practice database (SQLite) and uses the orders table. You can solve it in the browser, with nothing to install.

Solve This SQL Challenge

The task

Find the longest streak of days on which revenue was higher than on the previous order day. Show when it started, when it ended and how many days it lasted.

A correct answer returns 1 row with 3 columns: streak_start, streak_end, streak_days.

Tables used in this challenge

orders 200 rows

id INTEGER (PK) · user_id INTEGER · total REAL · discount REAL · item_count INTEGER · status TEXT · order_date TEXT · shipped_date TEXT · shipping_city TEXT · shipping_country TEXT

First 3 rows of the orders table
iduser_idtotaldiscountitem_countstatusorder_dateshipped_dateshipping_cityshipping_country
1432638.110.253cancelled2024-04-24NULLLos AngelesUSA
2829470.195delivered2024-03-152023-04-09ChicagoUSA
3136683.240.082delivered2023-02-152023-07-10Los AngelesUSA

The first rows of each table are shown. See how all the tables relate on the database schema page.

Expected output

Expected output of the challenge
streak_startstreak_endstreak_days
2023-03-032023-03-184

The complete result: 1 row. This is the output of the model solution on the practice database.

What you practise

  • Filtering rows with a WHERE condition
  • Summarising many rows into one value with COUNT, SUM, AVG, MIN or MAX
  • Grouping rows with GROUP BY to get one result row per group
  • Naming an intermediate result with a WITH clause and reading from it
  • Turning conditions into values with CASE WHEN
  • Reading the previous row's value with LAG()
  • Accumulating a value row by row with an ordered window
  • Sorting the result with ORDER BY
  • Returning only the first rows with LIMIT

Hint and approach

Try the challenge first. Open these only when you need them.

Show the hint

Flag each increase with LAG, start a new group at every non-increase with a running SUM, then count the rows per group

Show the step-by-step approach

How the model solution is built, in the order the database evaluates it:

  1. Define a common table expression named daily:
    1. Read from the orders table.
    2. Group the rows by order_date, so the result has one row for each value.
    3. For each group, compute SUM(total).
  2. Define a common table expression named flagged:
    1. Read from the daily CTE.
    2. Fetch revenue from the previous row with LAG(), ordered by order_date. Where there is no such row the value is NULL.
    3. Label each row with a CASE expression (inc): the conditions are tested in order and the first one that is true decides the value.
  3. Define a common table expression named grouped:
    1. Read from the flagged CTE.
    2. Compute SUM(CASE WHEN inc = 0 THEN 1 ELSE 0 END) as a running window function: it covers the rows from the start up to the current one, ordered by order_date.
  4. Read from the grouped CTE.
  5. Filter in WHERE: keep the rows where inc equals 1.
  6. Group the rows by grp, so the result has one row for each value.
  7. For each group, compute MIN(order_date), MAX(order_date) and COUNT(*).
  8. Sort the result by streak_days descending.
  9. Keep only the first row with LIMIT 1.

Model solution

Checking your session…

What to watch for

Three steps: flag each day as an increase or not with LAG, give every run its own number with a running count of the non-increases, then count the flagged rows per run. Days without orders do not exist in the table, so consecutive means consecutive order days.

  • Running totals. With ORDER BY and no frame, rows that tie on the sort column are added together in one step. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a strict row-by-row total.
  • LAG(). LAG() returns NULL for the first row of each partition, and any arithmetic with that NULL is NULL too. Supply a default as the third argument if you need a number.
  • CTE (WITH). A CTE exists only for the single statement it is attached to. It is not a temporary table and cannot be reused by the next query.
Solve This SQL Challenge

Same technique, other tables

More practice on this pattern with different columns:

Keep learning