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.