Calculating month-over-month customer lifetime value, transaction ranks, and running 30-day moving average revenue across millions of orders using Window Functions, CTEs, and conditional aggregations.
WITH monthly_user_spend AS (
SELECT
u.user_id,
u.country,
DATE_TRUNC('month', o.created_at) AS order_month,
SUM(o.amount) AS total_monthly_spend,
COUNT(o.order_id) AS total_orders
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'COMPLETED'
GROUP BY u.user_id, u.country, DATE_TRUNC('month', o.created_at)
)
SELECT
user_id,
country,
order_month,
total_monthly_spend,
-- Window Function: Dense Rank by spend within country per month
DENSE_RANK() OVER (
PARTITION BY country, order_month
ORDER BY total_monthly_spend DESC
) AS country_spend_rank,
-- Window Function: Cumulative running spend per user across lifetime
SUM(total_monthly_spend) OVER (
PARTITION BY user_id
ORDER BY order_month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_lifetime_spend,
-- Window Function: Previous month spend to compute growth
LAG(total_monthly_spend, 1, 0) OVER (
PARTITION BY user_id
ORDER BY order_month
) AS prev_month_spend
FROM monthly_user_spend
ORDER BY country, order_month, country_spend_rank;Visual representation of control loops, memory layout, and execution flow for Advanced SQL, Joins, Window Functions & Subqueries.
1. FROM & JOINs: Cross product of tables, filtered by ON predicates. 2. WHERE: Row-level filtering prior to grouping (cannot use aggregate functions). 3. GROUP BY: Aggregates matching rows into single summary buckets. 4. HAVING: Filters aggregated groups (evaluates aggregate predicates like SUM() > 1000). 5. SELECT: Evaluates expressions, column aliases, and window functions. 6. DISTINCT: Deduplicates identical output tuples. 7. ORDER BY: Sorts final projected tuples (can access SELECT aliases). 8. LIMIT / OFFSET: Paginates the final output dataset.
INNER JOIN: Returns rows where ON predicate matches in both tables. LEFT (OUTER) JOIN: Returns all rows from left table; unmatched right columns filled with NULL. RIGHT (OUTER) JOIN: Returns all rows from right table; unmatched left columns filled with NULL. FULL (OUTER) JOIN: Returns all rows when match exists in either table. CROSS JOIN: Cartesian product producing |A| * |B| rows. SELF JOIN: Joining a table to itself using distinct aliases (e.g. Employee.manager_id = Manager.employee_id). Non-Equi JOIN: Joins using comparison operators (<, >, BETWEEN) rather than equality.
Scalar Subquery: Returns 1 row, 1 column; usable anywhere a literal value is expected. Multi-Row Subquery: Evaluated with IN, ANY, ALL, NOT IN (Beware: NOT IN returns empty set if subquery contains a single NULL value!). Correlated Subquery: Subquery references outer query attributes, executing once per outer tuple (unless unnested by optimizer). CTEs (WITH clause): Improves modularity, reusability, and enables recursive graph traversal with WITH RECURSIVE.
Unlike GROUP BY (which collapses multiple rows into a single row), Window Functions compute calculations over a sliding partition of rows while PRESERVING individual row identity. Key ranking functions: ROW_NUMBER() (unique consecutive integers 1,2,3,4), RANK() (skips numbers on ties: 1,2,2,4), DENSE_RANK() (does not skip: 1,2,2,3), NTILE(k) (buckets into k percentiles). Value functions: LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE().
| Feature / Dimension | WHERE Clause | HAVING Clause |
|---|---|---|
| Execution Stage | Evaluated before GROUP BY (filters individual rows before aggregation) | Evaluated after GROUP BY (filters aggregated group summary rows) |
| Aggregate Functions | CANNOT contain aggregate functions (e.g., WHERE SUM(salary) > 5000 is invalid SQL) | Can and typically does evaluate aggregate functions (e.g., HAVING COUNT(*) > 5) |
| Index Utilization | Can directly utilize B+ Tree indexes on table columns to prune row scans | Operates on temporary grouped memory structures created during query execution |
Detailed answers, interviewer pro tips, key takeaway summaries, and code examples formulated for technical rounds.
✅ Correction: Because WHERE executes before SELECT in the logical evaluation pipeline, column aliases created in SELECT do not exist yet when WHERE is evaluated. (Use a CTE or repeat the expression).
✅ Correction: If you write `FROM A LEFT JOIN B ON A.id = B.a_id WHERE B.status = 1`, any row where B was unmatched (NULL) fails `NULL = 1`, silently turning your LEFT JOIN into an INNER JOIN. Place `AND B.status = 1` inside the ON clause.
✅ Correction: UNION performs an expensive sorting and deduplication step. If duplicate rows are impossible or acceptable, always use UNION ALL to eliminate unnecessary overhead.
Declarative query evaluation pipelines, relational joins, and analytical window functions.