Harder SQL Questions

The SQL round is where many strong candidates stumble. Not because the questions are impossible, but because they've only practised simple ones. Here are two harder patterns, which show up all the time.

• SQL round stumbles
• They only practised simple ones
• Two harder patterns

Best day, with a moving average

The question is, for each city, find the best day ever, and compare it with the seven day average. We build this in steps. The first step gets daily revenue. The second adds a moving average, with a window frame that says, six rows before, and the current one. Then qualify keeps the top row per city, without an extra sub query. Run it. Each cafe's best day, along with its typical level. Build hard queries step by step, with clear names, and say your plan out loud.

import duckdb

print(duckdb.sql("""
    WITH d AS (
        SELECT city, date, SUM(revenue) AS rev FROM 'sales.csv' GROUP BY 1, 2
    ), r AS (
        SELECT *, AVG(rev) OVER (PARTITION BY city ORDER BY date
                                 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS week_avg
        FROM d
    )
    SELECT city, strftime(date, '%Y-%m-%d') AS day, ROUND(rev) AS rev, ROUND(week_avg) AS avg7
    FROM r QUALIFY ROW_NUMBER() OVER (PARTITION BY city ORDER BY rev DESC) = 1
    ORDER BY rev DESC LIMIT 3
""").df().to_string(index=False))

  city        day    rev   avg7
 Osaka 2025-12-14 1784.0 1435.0
Bergen 2025-12-13 1521.0 1216.0
 Milan 2025-12-21 1514.0 1246.0

A running total

Here's another classic. Running totals. We count signups each month, and add a window sum over the counts. Notice the window function wraps an aggregate, which feels strange the first time. Run it. Each month adds to the last. Practise these patterns until they feel natural. Running totals, moving averages, top per group, and gaps between events.

import duckdb

print(duckdb.sql("""
    WITH c AS (SELECT customer_id, CAST(signup AS DATE) AS d FROM 'customers.csv')
    SELECT strftime(d, '%Y-%m') AS month, COUNT(*) AS signups,
           SUM(COUNT(*)) OVER (ORDER BY strftime(d, '%Y-%m')) AS running_total
    FROM c GROUP BY 1 ORDER BY 1 LIMIT 4
""").df().to_string(index=False))

  month  signups  running_total
2023-01      166          166.0
2023-02      176          342.0
2023-03      201          543.0
2023-04      195          738.0

Summary