Tricky reports

Part of the free LogicWiz SQL & Databases course, Interview pack: SQL interview problems. The queries and practice run in the app on real Postgres.

The last problem of the round is never a pure SELECT. It is "swap every pair of seats", "the median salary without a MEDIAN function", or "split clicks into sessions". Each has one trick; once you know it, it is five lines.

You'll learn to

• Pivot rows into columns with FILTER, and unpivot back
• Compute medians and rates correctly
• Swap adjacent rows and find consecutive free seats
• Split events into sessions with LAG and a running SUM

Learn

Pivot: one column per status

SELECT c.city,
       COUNT(*) FILTER (WHERE o.status = 'delivered') AS delivered,
       COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled,
       COUNT(*) FILTER (WHERE o.status = 'pending')   AS pending
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY c.city
ORDER BY c.city;

Each FILTER counts only its own rows inside the group. COUNT gives 0, never NULL, when nothing matches.

Median: an ordered-set aggregate

SELECT d.name AS department,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY e.salary) AS median_salary,
       ROUND(AVG(e.salary)) AS avg_salary
FROM employees AS e
JOIN departments AS d ON d.dept_id = e.dept_id
GROUP BY d.name
ORDER BY d.name;

PERCENTILE_CONT(0.5) averages the two middle values when the count is even; PERCENTILE_DISC(0.5) picks an actual value. Engineering's median (₹1,80,000) is far below its average, because the CEO's salary pulls the average up. That is why reports use the median.

Sessions: flag the starts, then add up the flags

SELECT user_id, clicked_at,
       CASE WHEN clicked_at - LAG(clicked_at) OVER w <= INTERVAL '30 minutes'
            THEN 0 ELSE 1 END AS is_new
FROM clicks
WINDOW w AS (PARTITION BY user_id ORDER BY clicked_at)
ORDER BY user_id, clicked_at;

A click starts a new session if it is the first one (LAG is NULL) or comes more than 30 minutes after the previous one. A running SUM(is_new) then numbers the sessions 1, 2, 3.

MySQL has no FILTER and no PERCENTILE_CONT. Pivot with SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END), and find a median by numbering rows and averaging the middle one or two.

SUM(CASE WHEN … THEN 1 END) with no ELSE gives NULL, not 0, for a group with no matches. Add ELSE 0, or use COUNT(*) FILTER (WHERE …).

In one line

Pivot with COUNT(*) FILTER; median with PERCENTILE_CONT(0.5); swap seats by changing ids; sessions = LAG flag + running SUM.

Interview corner

How do you find a median in SQL?

In Postgres, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x). Without it, number the rows with ROW_NUMBER and COUNT them, then average the rows whose number is in (n + 1) / 2 and (n + 2) / 2 (integer division), which picks one middle row for odd n and two for even n.

How do you split clickstream events into sessions?

Use LAG to get each event's previous timestamp per user, flag a new session when the gap exceeds the timeout (or there is no previous event), then a running SUM(flag) OVER (PARTITION BY user ORDER BY ts) gives a session number. Group by user and session number for start, end and counts.

How do you pivot rows into columns?

Conditional aggregation: COUNT(*) FILTER (WHERE status = 'x') in Postgres, or SUM(CASE WHEN status = 'x' THEN 1 ELSE 0 END) anywhere. The column list is fixed in the query; a truly dynamic pivot needs the app (or dynamic SQL) to build it.