SQL

Solution

Use row_number() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) in a CTE, then keep rn = 1. Window functions run after WHERE, so a filter on rn in the same SELECT is invalid (unless the dialect has QUALIFY). Interviewers want that evaluation order, not a clever one-liner.

Solution

Flag a new session when occurred_at − lag(occurred_at) over the customer is greater than 30 minutes (or null for the first event). Then session_id = a running sum of that flag. That is islands-and-gaps. Do not group by a floored timestamp — that splits real sessions and merges strangers.

Solution

RANK() (and DENSE_RANK()) ties share a rank, so you can return more than three rows when amounts match. ROW_NUMBER() always returns exactly N. Say when you want ties (leaderboard) versus a hard cap (sample / pagination).

Solution

Count rows before and after the join. If the fact grew, the “unique” key on the dimension is not unique at the grain you joined. Check duplicates with GROUP BY key HAVING count(*) > 1, then decide: aggregate the dimension or pick the correct role-playing key. Never “fix” it with DISTINCT on the fact.

Solution

The inner SUM is the GROUP BY aggregation (daily revenue). The window SUM then walks those grouped rows. You need both: group to the day first, then window. A bare SUM(amount) OVER (ORDER BY day) without grouping double-counts every order row.

Solution

NOT IN with a NULL in the list returns empty. Anti-joins should be NOT EXISTS or a LEFT JOIN … WHERE right.key IS NULL. MERGE match conditions that compare nullable keys silently miss rows. Say this out loud; it is a common take-home trap.

Solution

If the query already filters a selective column and still scans everything, storage layout (partition, cluster, index) is the lever. If it selects * and joins at the wrong grain, rewrite first. Do not add a clustering key to hide a CROSS JOIN.

Solution

QUALIFY is cleaner in Snowflake/BigQuery when the dialect has it. A CTE is portable and easier to debug. Either is fine; filtering rn in WHERE on the same SELECT is not.

Solution

Read the plan / bytes scanned / partitions assigned. Add the partition column to the filter (or clustering). EXPLAIN or the warehouse profile — not a guess.

Solution

NOT EXISTS (or LEFT JOIN … IS NULL). NOT IN dies on NULLs. That is the answer they want.

Solution

Many engines restrict DISTINCT in windows or make it expensive. A better story is a daily uniques table then a rolling sum of an inclusion flag — or a bitmap if the warehouse has one. Say the grain first.

Solution

January set MINUS February set, or EXISTS January AND NOT EXISTS February. A LEFT JOIN to February WHERE feb.key IS NULL is the anti-join they want. IN (SELECT …) dies if February keys contain NULL.

Solution

The window walks existing days, not calendar days, so Sunday is skipped and Monday’s “7-day” is six days plus a hole. Generate a complete date dimension and LEFT JOIN facts onto it before the window.

Solution

CONT interpolates; DISC returns an actual row value. Finance medians on sparse amounts should say which. “The median” without the function is a junior tell.

Solution

ISO vs locale weeks, incomplete first/last weeks, and time zones. Compare on a date spine with is_partial_week, or use a 7-day trailing window from today instead of calendar weeks.

Solution

When one side is tiny and indexed (point lookups), nested loop + index can beat building a hash. Warehouse SQL usually hashes; OLTP Postgres often nested-loops. Say “probe side size,” not “hash is always faster.”

Solution

Legacy Hive-style folders or “we never ALTER TABLE.” Partition/cluster one table. UNION ALL also hides a missing month until someone notices the chart drop.

Solution

Default LAG sees NULL as a value, so a missing day poisons the previous-day comparison. IGNORE NULLS (where supported) or a date spine plus COALESCE. Say what “previous” means: previous row or previous calendar day.

Solution

Daily unique-user fact (user_id, day). Then for each day, count distinct over the last 30 daily rows — still heavy — or HyperLogLog / bitmap sketches if approximate is allowed. Say the SLA: exact vs 2% error.

Solution

When you need every scenario (FX rates, every A/B bucket) and the small side is truly small. If the “parameter” table is 50k rows you built a cartesian bomb. Always count rows after.

Solution

FILTER is readable and often plans the same as SUM(CASE WHEN …). Interviewers want you to know both, and that MySQL/SQL Server need CASE. Portability is a real answer.