Question bank for data roles, from basic to advanced

GROUP BY collapses rows together; a window function performs the aggregate calculation but keeps the number of rows.

SELECT order_id, customer_id, amount,
       SUM(amount)  OVER (PARTITION BY customer_id) AS total_by_cust,
       amount / SUM(amount) OVER (PARTITION BY customer_id) AS share
FROM orders;

Whenever you want “each row's share of its group total” or “comparison with the group average” without losing rows, a window function is the answer.

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
       RANK()       OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM players;

ROW_NUMBER is always unique; RANK skips after ties (1, 1, 3); DENSE_RANK doesn't skip (1, 1, 2). Use ROW_NUMBER to “remove duplicate records” and DENSE_RANK for “true ranking”.

A CTE is WITH name AS (...): a named temporary result that makes a query step-by-step and readable, and can be referenced several times.

WITH monthly AS (
    SELECT DATE_TRUNC('month', order_date) AS m, SUM(amount) AS rev
    FROM orders WHERE status = 'paid'
    GROUP BY 1
), with_growth AS (
    SELECT m, rev, LAG(rev) OVER (ORDER BY m) AS prev_rev
    FROM monthly
)
SELECT m, rev, ROUND(100.0 * (rev - prev_rev) / NULLIF(prev_rev,0), 1) AS growth_pct
FROM with_growth ORDER BY m;

In Postgres versions before 12, a CTE was always materialized (an optimization fence). From 12 onward the default is inline unless you write MATERIALIZED. If they ask “are CTEs slow?”, say exactly this.

An index (usually a B-Tree) is a sorted structure that brings lookups down from O(n) to O(log n), but it slows down writes and takes up space.

The index is not used when:

• You apply a function to the indexed column: WHERE YEAR(order_date)=2024 → write a range instead.
• LIKE with a leading %: LIKE '%foo'.
• Cardinality is low (for example a gender column) and a large share of the table comes back; then a Seq Scan is cheaper.
• An implicit type conversion happens (comparing a numeric column with a string).

-- Bad: the index has no effect
WHERE DATE_TRUNC('year', order_date) = '2024-01-01'
-- Good: sargable
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

Using the term sargable earns you points: a condition the engine can apply directly on the index.

CREATE INDEX idx_orders ON orders (customer_id, order_date);

• A query with WHERE customer_id = 5 → the index is used.
• With customer_id = 5 AND order_date > '2024-01-01' → it is fully used.
• With only order_date > ... → usually not used, because the first column isn't in the condition.

Order the columns as “equality columns first, range column after”.

EXPLAIN ANALYZE
SELECT c.city, COUNT(*) FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01' GROUP BY c.city;

What you look for:

• A big gap between estimated rows and actual rows → statistics are stale; run ANALYZE.
• Seq Scan on a large table with a selective filter → a candidate for an index.
• High Rows Removed by Filter → the filter is applied too late.
• Nested Loop with many loops over a large table → usually a sign of trouble.

• 1NF atomic values, no repeating groups.
• 2NF 1NF + full dependency on the whole primary key.
• 3NF 2NF + no transitive dependency (non-key column on non-key column).

In OLTP, normalization reduces redundancy and inconsistency. In OLAP and data warehouses, many JOINs are expensive, so with a star schema we deliberately repeat some columns in dimension tables to make analytical queries fast.

If the role is analytics/BI, make sure you know the star schema and the difference between a fact table and a dimension table — a fact holds numeric measures and foreign keys, a dimension holds descriptive attributes.

-- 1. IN
SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);
-- 2. EXISTS (correlated)
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
-- 3. JOIN — watch out for row multiplication
SELECT DISTINCT c.* FROM customers c JOIN orders o USING (customer_id);

EXISTS stops as soon as it finds the first match and is safe with NULLs. A JOIN multiplies left rows if the right table has several matching rows — the classic “why did my total sales double” trap.

• Atomicity — a transaction either completes fully or not at all.
• Consistency — the database moves from one valid state to another valid state.
• Isolation — concurrent transactions don't affect each other in unintended ways.
• Durability — after COMMIT, the data survives even a power failure.

Isolation levels from weakest to strongest: READ UNCOMMITTED → READ COMMITTED (Postgres default) → REPEATABLE READ → SERIALIZABLE. The matching problems: dirty read, non-repeatable read, phantom read.

-- 1) Scalar: returns one value, runs once
SELECT name, (SELECT AVG(amount) FROM orders) AS overall_avg FROM customers;

-- 2) Correlated: depends on the outer row, evaluated per row
SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS n
FROM customers c;

-- 3) In FROM (derived table): behaves like a temporary table
SELECT city, AVG(n) FROM (
  SELECT customer_id, city, COUNT(*) AS n
  FROM orders JOIN customers USING (customer_id) GROUP BY 1, 2
) t GROUP BY city;

The second is the most expensive. It can usually be rewritten as a LEFT JOIN + GROUP BY, which gets a much better plan.

-- Slow on deep pages: it must read and discard 100,000 rows
SELECT * FROM orders ORDER BY order_id LIMIT 20 OFFSET 100000;

-- Keyset pagination: fast and stable
SELECT * FROM orders
WHERE order_id > :last_seen_id
ORDER BY order_id
LIMIT 20;

The second problem with OFFSET is that if a record is inserted between two requests, some rows are repeated or skipped. The keyset approach solves both problems.

SELECT COALESCE(city, 'All cities') AS city,
       COALESCE(status, 'all')    AS status,
       SUM(amount) AS revenue
FROM orders JOIN customers USING (customer_id)
GROUP BY ROLLUP (city, status)
ORDER BY city NULLS LAST;

In a single scan you get the details as well as the subtotals and the grand total. The alternative is a UNION ALL of several queries, which costs several times as much.

WHERE name LIKE 'Moh%'         -- starts with, case-sensitive
WHERE email ILIKE '%@GMAIL.COM' -- case-insensitive (Postgres)
WHERE name ~ '^Moh'             -- regular expression
WHERE to_tsvector('simple', body) @@ plainto_tsquery('simple', 'discount')

The pattern 'x%' can use a B-Tree index but '%x' cannot. To search the middle of a text you need a trigram index or full-text search.

SELECT 7 / 2            AS int_div,      -- 3  integer division
       7.0 / 2          AS real_div,     -- 3.5
       CAST(7 AS numeric) / 2 AS explicit,
       '2024-08-31'::date AS d,
       amount::text     AS as_text
FROM orders LIMIT 1;

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  -- If either one fails:
  -- ROLLBACK;
COMMIT;

It is useful in data pipelines too: put deleting a day's partition and re-inserting it in one transaction so users never see a half-finished state (an empty table).

WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) AS rn
  FROM orders o
)
SELECT customer_id, order_id, order_date, amount
FROM ranked WHERE rn = 1;

I added the order_id DESC tie-breaker so the result is deterministic if there are two orders on the same day. In Postgres, the faster way is DISTINCT ON:

SELECT DISTINCT ON (customer_id) *
FROM orders ORDER BY customer_id, order_date DESC;

SELECT d, rev,
       ROUND(AVG(rev) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 0) AS ma7
FROM daily_revenue ORDER BY d;

If days without sales are missing from the table, the moving average is wrong. The fix: build a calendar with generate_series and LEFT JOIN to it. Say this so it's clear you've thought about missing data.

SELECT g.d, COALESCE(r.rev, 0) AS rev
FROM generate_series('2024-08-01'::date, '2024-08-31', '1 day') g(d)
LEFT JOIN daily_revenue r ON r.d = g.d;

WITH m AS (
  SELECT DATE_TRUNC('month', order_date)::date AS mon, SUM(amount) AS rev
  FROM orders WHERE status='paid' GROUP BY 1
)
SELECT mon, rev,
       LAG(rev) OVER (ORDER BY mon) AS prev,
       ROUND(100.0 * (rev - LAG(rev) OVER (ORDER BY mon))
             / NULLIF(LAG(rev) OVER (ORDER BY mon), 0), 1) AS mom_pct
FROM m ORDER BY mon;

NULLIF(x, 0) prevents a division-by-zero error. Always include this detail.

SELECT category, product_name, price
FROM (
  SELECT *, RANK() OVER (PARTITION BY category ORDER BY price DESC) AS r
  FROM products
) t
WHERE r = 1;

I deliberately used RANK rather than ROW_NUMBER here, because if two products have the same price both should appear. If the interviewer says “only one”, switch to ROW_NUMBER.

WITH f AS (
  SELECT user_id,
         MAX(CASE WHEN event_name='view'         THEN 1 ELSE 0 END) AS viewed,
         MAX(CASE WHEN event_name='add_to_cart'  THEN 1 ELSE 0 END) AS carted,
         MAX(CASE WHEN event_name='purchase'     THEN 1 ELSE 0 END) AS bought
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT SUM(viewed) AS viewers, SUM(carted) AS carters, SUM(bought) AS buyers,
       ROUND(100.0*SUM(carted)/NULLIF(SUM(viewed),0), 1)  AS view_to_cart_pct,
       ROUND(100.0*SUM(bought)/NULLIF(SUM(carted),0), 1)  AS cart_to_buy_pct
FROM f;

This version doesn't check the order of events. If they want an ordered funnel, compare each step's MIN(event_time) so the next step's time is later. Always state this limitation yourself.

WITH c AS (
  SELECT customer_id, DATE_TRUNC('month', MIN(order_date)) AS cohort
  FROM orders GROUP BY customer_id
), act AS (
  SELECT o.customer_id, c.cohort,
         DATE_TRUNC('month', o.order_date) AS mon
  FROM orders o JOIN c ON c.customer_id = o.customer_id
  GROUP BY 1,2,3
)
SELECT cohort,
       COUNT(DISTINCT customer_id) FILTER (WHERE mon = cohort) AS m0,
       COUNT(DISTINCT customer_id) FILTER (WHERE mon = cohort + INTERVAL '1 month') AS m1,
       ROUND(100.0 * COUNT(DISTINCT customer_id) FILTER (WHERE mon = cohort + INTERVAL '1 month')
             / NULLIF(COUNT(DISTINCT customer_id) FILTER (WHERE mon = cohort), 0), 1) AS retention_pct
FROM act GROUP BY cohort ORDER BY cohort;

FILTER (WHERE ...) is a cleaner, Postgres-specific version of SUM(CASE WHEN ...). In MySQL you'd have to write CASE.

SELECT c.city,
       SUM(o.amount) FILTER (WHERE EXTRACT(quarter FROM o.order_date)=1) AS q1,
       SUM(o.amount) FILTER (WHERE EXTRACT(quarter FROM o.order_date)=2) AS q2,
       SUM(o.amount) FILTER (WHERE EXTRACT(quarter FROM o.order_date)=3) AS q3,
       SUM(o.amount) FILTER (WHERE EXTRACT(quarter FROM o.order_date)=4) AS q4
FROM orders o JOIN customers c USING (customer_id)
WHERE o.status='paid'
GROUP BY c.city ORDER BY 2 DESC NULLS LAST;

NULLS LAST matters because in Postgres a descending sort puts NULLs first by default.

SELECT customer_id,
       MIN(order_date)                        AS first_order,
       MAX(order_date)                        AS last_order,
       COUNT(*)                               AS orders,
       CURRENT_DATE - MAX(order_date)         AS days_since_last
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY days_since_last DESC;

The last column is the recency measure from RFM analysis; the customers at the top of this list are candidates for a win-back campaign.

WITH t AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders WHERE status = 'paid' GROUP BY customer_id
)
SELECT CASE WHEN total >= 10000000 THEN 'gold'
            WHEN total >=  3000000 THEN 'silver'
            WHEN total >=  500000  THEN 'bronze'
            ELSE 'new' END AS tier,
       COUNT(*)   AS customers,
       SUM(total) AS revenue
FROM t GROUP BY 1
ORDER BY revenue DESC;

The order of the CASE conditions matters: start with the largest threshold, otherwise everything falls into the first condition.

WITH per_order AS (
  SELECT o.order_id,
         COUNT(oi.product_id)              AS items,
         SUM(oi.qty * oi.unit_price)       AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'paid'
  GROUP BY o.order_id
)
SELECT ROUND(AVG(items), 2) AS avg_items,
       ROUND(AVG(total))    AS avg_basket,
       COUNT(*)             AS orders
FROM per_order;

The pattern “aggregate at the fine grain first, then aggregate at the higher level” is the standard way to avoid double counting. If you average directly over the JOIN, orders with many items get more weight.

-- trades(trade_id, user_id, trade_time, symbol, amount)
-- transactions(txn_id, user_id, txn_time, txn_type, amount)   txn_type: deposit | withdrawal

WITH first_trade AS (
    SELECT user_id,
           MIN(trade_time) AS first_trade_at
    FROM trades
    GROUP BY user_id
)
SELECT f.user_id,
       f.first_trade_at,
       COALESCE(SUM(t.amount) FILTER (WHERE t.txn_type = 'deposit'),    0) AS deposit_before,
       COALESCE(SUM(t.amount) FILTER (WHERE t.txn_type = 'withdrawal'), 0) AS withdraw_before,
       COUNT(*) FILTER (WHERE t.txn_type = 'deposit')                       AS n_deposits,
       COALESCE(SUM(t.amount) FILTER (WHERE t.txn_type = 'deposit'),    0)
     - COALESCE(SUM(t.amount) FILTER (WHERE t.txn_type = 'withdrawal'), 0) AS net_funding,
       MIN(t.txn_time)                                                      AS first_txn_at,
       f.first_trade_at::date - MIN(t.txn_time)::date                       AS days_to_first_trade
FROM first_trade f
LEFT JOIN transactions t
       ON t.user_id  = f.user_id
      AND t.txn_time < f.first_trade_at      -- time condition inside ON, not in WHERE
GROUP BY f.user_id, f.first_trade_at
ORDER BY net_funding DESC;

Four design decisions to say out loud:

• The condition txn_time < first_trade_at is written inside ON, not in WHERE. In WHERE, users with no transactions before their first trade (like the last row) would disappear from the result entirely.
• COALESCE puts in zero so we don't see NULL in the report; but I deliberately left days_to_first_trade as NULL, because “zero days” and “never deposited” are two completely different things.
• The comparison is <, not <=: a transaction recorded at the exact moment of the trade does not count as “before”. If the business logic is different, ask right here.
• If the columns are timestamps with time zones, the day boundary must be computed in the reporting time zone, not UTC.

The Postgres-specific tool for this is DISTINCT ON, which also returns the first trade with its details: SELECT DISTINCT ON (user_id) * FROM trades ORDER BY user_id, trade_time. If they also want the symbol of the first trade, this version is better.

-- logs(id serial, num int)  — the ids are contiguous
SELECT DISTINCT l1.num AS consecutive_num
FROM logs l1
JOIN logs l2 ON l2.id = l1.id + 1 AND l2.num = l1.num
JOIN logs l3 ON l3.id = l1.id + 2 AND l3.num = l1.num;

-- A version robust to non-contiguous ids, using window functions
SELECT DISTINCT num
FROM (
    SELECT num,
           LAG(num)  OVER (ORDER BY id) AS prev_num,
           LEAD(num) OVER (ORDER BY id) AS next_num
    FROM logs
) t
WHERE num = prev_num AND num = next_num;

Prefer the second version and say why: if a row has been deleted, the ids have gaps and the id + 1 logic fails silently. Window functions look at the actual order, not at the id values.

WITH ranked AS (
    SELECT d.dept_name,
           e.name,
           e.salary,
           DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS r
    FROM employees e
    JOIN departments d ON d.dept_id = e.dept_id
)
SELECT dept_name, name, salary
FROM ranked
WHERE r <= 3
ORDER BY dept_name, salary DESC;

Choosing DENSE_RANK is deliberate: the problem asks for “the top three salary levels”, not “three people”. That is why two people with equal salaries both come in at rank 2. If they wanted exactly three rows, ROW_NUMBER is right — ask about exactly this distinction.

-- trips(id, client_id, driver_id, status, request_at)
-- users(users_id, banned, role)
SELECT t.request_at AS day,
       ROUND(
         AVG(CASE WHEN t.status LIKE 'cancelled%' THEN 1.0 ELSE 0.0 END), 2
       ) AS cancellation_rate,
       COUNT(*) AS trips
FROM trips t
JOIN users c ON c.users_id = t.client_id AND c.banned = 'No'
JOIN users d ON d.users_id = t.driver_id AND d.banned = 'No'
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.request_at
ORDER BY t.request_at;

Two subtle points: both sides of the trip (rider and driver) must be unbanned, so two separate JOINs are needed; and AVG over a zero/one expression is a cleaner version of SUM(...)/COUNT(*), with no integer-division problem because it uses 1.0.

WITH t AS (
    SELECT id, visit_date, people,
           ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM stadium
    WHERE people >= 100
),
grp AS (
    SELECT *, rn - id AS g          -- in a continuous run, this difference stays constant
    FROM t
)
SELECT id, visit_date, people
FROM grp
WHERE g IN (SELECT g FROM grp GROUP BY g HAVING COUNT(*) >= 3)
ORDER BY id;

This is the same “gaps and islands” pattern you saw in earlier sections. The difference between the row number and the original id forms the id of each continuous group. Once you truly understand this pattern, it solves dozens of different questions.

WITH cat AS (
  SELECT p.category, SUM(oi.qty * oi.unit_price) AS revenue
  FROM order_items oi JOIN products p USING (product_id)
  GROUP BY p.category
)
SELECT category,
       revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS share_pct,
       ROUND(100.0 * SUM(revenue) OVER (ORDER BY revenue DESC
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
             / SUM(revenue) OVER (), 1) AS cum_pct
FROM cat
ORDER BY revenue DESC;

The key trick is SUM(...) OVER () without PARTITION BY, which puts the grand total next to every row without needing a second subquery. The cumulative column shows the first three categories make up 87 percent of revenue — the Pareto rule.

WITH marked AS (
  SELECT user_id, event_time,
         CASE WHEN event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
                   > INTERVAL '30 minutes'
              OR LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
              THEN 1 ELSE 0 END AS is_new_session
  FROM events
),
sessions AS (
  SELECT user_id, event_time,
         SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time
                                   ROWS UNBOUNDED PRECEDING) AS session_no
  FROM marked
)
SELECT user_id, session_no,
       MIN(event_time)                            AS started_at,
       MAX(event_time)                            AS ended_at,
       COUNT(*)                                   AS events,
       MAX(event_time) - MIN(event_time)          AS duration
FROM sessions
GROUP BY user_id, session_no
ORDER BY user_id, session_no;

It is the same “break flag, then running sum” pattern you saw in the chain problems. The 30-minute threshold is a convention; if the interviewer asks why, say it should be derived from the actual distribution of gaps between events, not from habit.

-- a) Simplest: OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 2 LIMIT 1;                 -- third highest

-- b) With a window function, works for any N
WITH r AS (
  SELECT DISTINCT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT salary FROM r WHERE rnk = 3;

-- c) A function version that returns NULL if it doesn't exist
CREATE OR REPLACE FUNCTION nth_salary(n int) RETURNS numeric AS $$
  SELECT salary FROM (
    SELECT DISTINCT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rnk
    FROM employees) t
  WHERE rnk = n;
$$ LANGUAGE sql;

Three things the interviewer checks: using DISTINCT or DENSE_RANK for equal salaries, the right behavior when N exceeds the number of existing levels (it should return NULL, not an error), and knowing that DENSE_RANK differs from RANK here.

-- The three best-selling products in each category
WITH ranked AS (
  SELECT p.category,
         p.product_name,
         SUM(oi.qty * oi.unit_price) AS revenue,
         ROW_NUMBER() OVER (PARTITION BY p.category
                            ORDER BY SUM(oi.qty * oi.unit_price) DESC,
                                     p.product_id) AS rn
  FROM order_items oi
  JOIN products p USING (product_id)
  GROUP BY p.category, p.product_name, p.product_id
)
SELECT category, product_name, revenue
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;

-- The compact Postgres version when you only want the top one
SELECT DISTINCT ON (category) category, product_name, revenue
FROM product_revenue
ORDER BY category, revenue DESC;

Explain the choice between ROW_NUMBER, RANK and DENSE_RANK out loud: ROW_NUMBER if you want exactly three rows, DENSE_RANK if you want “the top three levels”, and RANK if ties must both appear. Add a secondary tie-breaker too so the result is deterministic.

SELECT order_date::date AS d,
       SUM(amount)                                    AS daily,
       SUM(SUM(amount)) OVER (ORDER BY order_date::date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,
       -- Cumulative within each month, with a reset
       SUM(SUM(amount)) OVER (PARTITION BY DATE_TRUNC('month', order_date)
            ORDER BY order_date::date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS mtd
FROM orders
WHERE status = 'paid'
GROUP BY 1, DATE_TRUNC('month', order_date)
ORDER BY 1;

The combination SUM(SUM(...)) OVER (...) looks odd at first: the inner aggregate runs per group and the window function runs over the grouped results. Explaining this execution order scores well.

WITH cal AS (                       -- build a complete calendar first
  SELECT g::date AS d
  FROM generate_series(DATE '2024-08-01', DATE '2024-08-31', '1 day') g
),
daily AS (
  SELECT c.d, COALESCE(SUM(o.amount), 0) AS rev
  FROM cal c
  LEFT JOIN orders o ON o.order_date::date = c.d AND o.status = 'paid'
  GROUP BY c.d
)
SELECT d, rev,
       ROUND(AVG(rev) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)) AS ma7,
       ROUND(AVG(rev) OVER (ORDER BY d ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)) AS ma30,
       COUNT(*)  OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS window_days
FROM daily
ORDER BY d;

Two vital points: build the calendar with generate_series so days without sales become zero (otherwise the average is over “7 days that have data”, not 7 calendar days), and the window_days column, which shows that at the start of the range the window isn't full yet and those values aren't reliable.

-- a) Portable to every database
SELECT customer_id, order_id, order_date, amount
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY order_date DESC, order_id DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

-- b) The fast Postgres version
SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, order_date DESC, order_id DESC;

-- c) Without window functions (older databases)
SELECT o.*
FROM orders o
JOIN (SELECT customer_id, MAX(order_date) AS mx
      FROM orders GROUP BY customer_id) m
  ON m.customer_id = o.customer_id AND m.mx = o.order_date;

Always add the secondary tie-breaker (order_id DESC) so that if two orders fall on the same day, the result is deterministic and reproducible. Approach “c” has exactly this problem: if two orders share a date, both come back.

-- a) Always SELECT first to see what will be deleted
WITH dup AS (
  SELECT customer_id,
         ROW_NUMBER() OVER (PARTITION BY email
                            ORDER BY created_at DESC, customer_id DESC) AS rn
  FROM customers
)
SELECT COUNT(*) FROM dup WHERE rn > 1;

-- b) Delete, keeping the newest
WITH dup AS (
  SELECT customer_id,
         ROW_NUMBER() OVER (PARTITION BY email
                            ORDER BY created_at DESC, customer_id DESC) AS rn
  FROM customers
)
DELETE FROM customers
WHERE customer_id IN (SELECT customer_id FROM dup WHERE rn > 1);

-- c) Rebuild the table: faster than DELETE on large volumes
CREATE TABLE customers_clean AS
SELECT DISTINCT ON (email) * FROM customers ORDER BY email, created_at DESC;

-- d) Prevent duplicates in the future
CREATE UNIQUE INDEX CONCURRENTLY ux_customers_email ON customers (lower(email));

Part “d” is the most important part of the answer. Removing duplicates without adding a unique constraint means you'll be doing the same job again next week. Saying this shows the difference between an analyst and a data engineer.

-- a) Unmatched rows: customers without orders
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- b) Days without sales
SELECT g::date AS missing_day
FROM generate_series(DATE '2024-08-01', DATE '2024-08-31', '1 day') g
LEFT JOIN orders o ON o.order_date::date = g::date
WHERE o.order_id IS NULL;

-- c) Gaps in an id sequence
SELECT prev_id + 1 AS gap_start, id - 1 AS gap_end, id - prev_id - 1 AS missing_cnt
FROM (SELECT id, LAG(id) OVER (ORDER BY id) AS prev_id FROM invoices) t
WHERE id - prev_id > 1;

-- d) Two-way differences between two sources
SELECT COALESCE(a.id, b.id) AS id,
       CASE WHEN a.id IS NULL THEN 'target only' ELSE 'source only' END AS side
FROM source_t a
FULL JOIN target_t b USING (id)
WHERE a.id IS NULL OR b.id IS NULL;

import sys
lst = [i*i for i in range(1_000_000)]
gen = (i*i for i in range(1_000_000))
print(sys.getsizeof(lst), sys.getsizeof(gen))

def read_big(path):           # process a large file without loading it all
    with open(path, encoding='utf-8') as f:
        for line in f:
            yield line.rstrip('\n')

A generator is lazy: it produces values on demand and uses no memory for them, but it can be iterated only once and has no len(). It is the natural choice for multi-gigabyte files or a Kafka stream.

import time, functools

def timed(fn):
    @functools.wraps(fn)              # keep the original function's name and docstring
    def wrapper(*args, **kwargs):
        t0 = time.perf_counter()
        result = fn(*args, **kwargs)
        print(f"{fn.__name__} took {time.perf_counter()-t0:.3f}s")
        return result
    return wrapper

@timed
def slow_sum(n):
    return sum(i*i for i in range(n))

slow_sum(3_000_000)

Without functools.wraps, the function's __name__ changes to “wrapper”, which breaks debugging and logging. Mentioning this earns points.

def report(title, *metrics, **options):
    print(title, metrics, options)

report("sales", "rev", "aov", currency="IRR", round_to=0)

In a call, the star does the reverse operation (unpacking): f(*lst) and f(**dct).

from contextlib import contextmanager
import time

@contextmanager
def stage(name):
    t0 = time.perf_counter()
    try:
        yield
    finally:
        print(f"[{name}] {time.perf_counter()-t0:.2f}s")

with stage("load"):
    time.sleep(0.5)

The main benefit: the finally block runs even when an exception occurs — vital for closing a database connection or releasing a lock.

import logging
logging.basicConfig(level=logging.INFO,
                    format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger(__name__)

def safe_div(a, b):
    try:
        return a / b
    except ZeroDivisionError:
        log.warning("division by zero, returning None")
        return None
    except TypeError as e:
        log.error("bad types: %s", e)
        raise                      # don't hide unexpected errors
    finally:
        log.debug("safe_div finished")

print(safe_div(10, 2), safe_div(1, 0))

Make two points: never write a bare except:, and raise again any error you can't handle meaningfully.

The Global Interpreter Lock lets only one thread execute Python bytecode at any moment.

• I/O-bound work (API calls, reading files, database queries) → threading or asyncio helps, because the GIL is released while waiting.
• CPU-bound work → multiprocessing, or C libraries such as NumPy that release the GIL.

from concurrent.futures import ThreadPoolExecutor, ProcessPoolExecutor
with ThreadPoolExecutor(max_workers=8) as ex:      # I/O
    results = list(ex.map(fetch_url, urls))
with ProcessPoolExecutor(max_workers=4) as ex:     # CPU
    results = list(ex.map(heavy_calc, chunks))

class Pipeline:
    version = "1.0"
    def __init__(self, name): self.name = name
    def run(self):                    return f"{self.name} running"
    @classmethod
    def from_config(cls, cfg):        return cls(cfg["name"])
    @staticmethod
    def validate(path):               return path.endswith(".csv")

p = Pipeline.from_config({"name": "daily"})
print(p.run(), Pipeline.validate("a.csv"))

classmethod is for alternative constructors (factories), and staticmethod is for helper functions related to the class but independent of its state.

from collections import Counter, defaultdict, deque

logs = ["view", "click", "view", "buy", "view", "click"]
print(Counter(logs).most_common(2))

by_user = defaultdict(list)
for uid, ev in [(1,"view"), (2,"buy"), (1,"click")]:
    by_user[uid].append(ev)          # no need to check whether the key exists
print(dict(by_user))

window = deque(maxlen=3)             # fixed-size queue
for x in [1,2,3,4,5]: window.append(x)
print(list(window))

import re
text = "Call: 09121234567 or 0913-765-4321, landline 02188776655"
pattern = r"09\d{2}[-\s]?\d{3}[-\s]?\d{4}"
nums = [re.sub(r"[-\s]", "", m) for m in re.findall(pattern, text)]
print(nums)

Common patterns: \d a digit, \w a word character, + one or more, ? optional, () a capturing group, (?:) a non-capturing group. Use re.compile for frequently used patterns so they are cached.

# Each order's share of the customer's total purchases — without losing rows
df["cust_total"] = df.groupby("customer_id")["amount"].transform("sum")
df["share"]      = df["amount"] / df["cust_total"]

# Rank within each group
df["rank_in_city"] = df.groupby("city")["amount"].rank(method="dense", ascending=False)

# Previous row and the difference
df = df.sort_values(["customer_id", "order_date"])
df["prev_amt"] = df.groupby("customer_id")["amount"].shift(1)
df["delta"]    = df["amount"] - df["prev_amt"]

Rule: agg collapses rows, transform keeps the original shape. This is exactly the difference between GROUP BY and OVER in SQL.

m = orders.merge(customers, on="customer_id", how="left",
                 validate="m:1", indicator=True)
print(m["_merge"].value_counts())
print("orders:", len(orders), "after merge:", len(m))

Two golden tools: validate="m:1", which raises an error if the relationship isn't what you think, and indicator=True, which shows how many rows didn't match. Writing these two in an interview looks very professional.

wide = df.pivot_table(index="city", columns="quarter",
                      values="amount", aggfunc="sum", fill_value=0)
print(wide.head(3))

long = wide.reset_index().melt(id_vars="city",
                               var_name="quarter", value_name="amount")
print(long.head(3))

pivot_table works with aggfunc and aggregates duplicate values; plain pivot raises an error if it sees duplicates.

ts = (df.set_index("order_date")
        .resample("D")["amount"].sum()
        .asfreq("D", fill_value=0))          # days without sales become zero
monthly = ts.resample("M").sum()
ma7 = ts.rolling(7, min_periods=1).mean()
print(monthly.head(3)); print(ma7.tail(3))

If you don't fill in the empty days, the moving average is computed over “7 days with data”, not 7 calendar days — a common and costly mistake.

print(df.isna().mean().sort_values(ascending=False).head())

df["amount"] = df["amount"].fillna(df.groupby("city")["amount"].transform("median"))
df["status"] = df["status"].fillna("unknown")
df["last_login"] = df["last_login"].ffill()      # only makes sense for a time series
df = df.dropna(subset=["customer_id"])            # a key must not be empty

The key sentence: “Before filling, I first ask why it is missing.” Random missingness, systematic missingness (a logging bug) and meaningful missingness (the user hasn't bought yet) need three completely different treatments.

q1, q3 = df["amount"].quantile([0.25, 0.75])
iqr = q3 - q1
lo, hi = q1 - 1.5*iqr, q3 + 1.5*iqr
out = df[(df.amount < lo) | (df.amount > hi)]
print(f"bounds: {lo:,.0f} .. {hi:,.0f}  | outliers: {len(out)} ({len(out)/len(df):.1%})")

Blindly removing outliers is a mistake. In sales data, large corporate orders are real. Say: “I flag them and check with the data owner, rather than quickly deleting them.”

print(df.memory_usage(deep=True).sum() / 1e6, "MB")
df["status"] = df["status"].astype("category")
df["customer_id"] = pd.to_numeric(df["customer_id"], downcast="integer")
df["amount"] = pd.to_numeric(df["amount"], downcast="float")
print(df.memory_usage(deep=True).sum() / 1e6, "MB")

Three main levers: category for low-cardinality text columns, numeric downcasting, and reading only the needed columns with usecols. For a very large file, chunksize or moving to Parquet/Polars/DuckDB.

a = np.array([[1, 2, 3], [4, 5, 6]])   # (2,3)
b = np.array([10, 20, 30])             # (3,)
print(a + b)
print(a - a.mean(axis=1, keepdims=True))   # row-wise normalization

Rule: dimensions are compared from the right and must be equal, or one of them must be 1. keepdims=True keeps the dimension so broadcasting works correctly.

x = np.array([5, 50, 500, 5000])
print(np.where(x > 100, "big", "small"))
print(np.select([x > 1000, x > 100], ["xl", "l"], default="s"))
print(np.clip(x, 10, 1000))            # clamp to a range

rng = np.random.default_rng(seed=42)
sims = rng.normal(loc=1_000_000, scale=200_000, size=100_000)
print(f"mean={sims.mean():,.0f}")
print(f"P5={np.percentile(sims,5):,.0f}  P95={np.percentile(sims,95):,.0f}")
print(f"P(>1.3M) = {(sims > 1_300_000).mean():.2%}")

Using default_rng(seed=...) instead of np.random.seed is the currently recommended approach and makes the result reproducible.

import requests, time

def fetch(url, params=None, retries=3):
    for i in range(retries):
        try:
            r = requests.get(url, params=params, timeout=10)
            r.raise_for_status()
            return r.json()
        except requests.HTTPError as e:
            if r.status_code == 429:            # rate limit
                time.sleep(2 ** i); continue
            raise
        except requests.RequestException:
            if i == retries - 1: raise
            time.sleep(2 ** i)                  # exponential backoff

data = fetch("https://api.example.com/orders", {"page": 1})
df = pd.json_normalize(data["items"])

Two points that score: always set a timeout (otherwise the script can hang forever) and use pd.json_normalize to flatten nested JSON.

from datetime import datetime, timedelta, timezone
s = "2024-08-31 14:20:00"
dt = datetime.strptime(s, "%Y-%m-%d %H:%M:%S")
print(dt.strftime("%Y/%m/%d"), (dt + timedelta(days=7)).date())

# In pandas, with time zones
ts = pd.to_datetime(df["created_at"], utc=True).dt.tz_convert("Asia/Tehran")
print(ts.dt.date.head(2).tolist(), ts.dt.dayofweek.head(2).tolist())

import json
from pathlib import Path

rec = {"city": "Tehran", "rev": 1_800_000}
Path("out.json").write_text(json.dumps(rec, ensure_ascii=False, indent=2),
                            encoding="utf-8")

# JSONL file (one record per line) — a common format for logs and Kafka dumps
rows = [json.loads(l) for l in Path("events.jsonl").read_text(encoding="utf-8").splitlines() if l]
df = pd.DataFrame(rows)

Don't forget ensure_ascii=False, otherwise non-ASCII text such as Persian is stored as escapes like \u062a.

def profile(df, top_n=3):
    rows = []
    for c in df.columns:
        s = df[c]
        rows.append({
            "column":   c,
            "dtype":    str(s.dtype),
            "nulls_%":  round(100 * s.isna().mean(), 2),
            "unique":   s.nunique(dropna=True),
            "unique_%": round(100 * s.nunique(dropna=True) / max(len(s), 1), 2),
            "top":      " | ".join(map(str, s.value_counts().head(top_n).index)),
            "min":      s.min() if pd.api.types.is_numeric_dtype(s) else "",
            "max":      s.max() if pd.api.types.is_numeric_dtype(s) else "",
        })
    return pd.DataFrame(rows).sort_values("nulls_%", ascending=False)

print(profile(df).head())

A unique_% of one hundred means a candidate key, and near zero means an almost constant column that is probably useless. Run this function at the start of every project; it takes twenty seconds and usually exposes several serious data problems right away.

from collections import defaultdict

orders = [
    {"city": "Tehran", "amount": 500}, {"city": "Mashhad", "amount": 300},
    {"city": "Tehran", "amount": 200}, {"city": "Mashhad", "amount": 100},
]

agg = defaultdict(lambda: {"total": 0, "count": 0})
for o in orders:
    agg[o["city"]]["total"] += o["amount"]
    agg[o["city"]]["count"] += 1

result = sorted(
    ({"city": c, **v, "avg": v["total"] / v["count"]} for c, v in agg.items()),
    key=lambda r: -r["total"])
for r in result: print(r)

This question is popular because it tests whether you understand grouping logic without pandas. defaultdict frees the code from repeated conditions.

from collections import Counter

def error_by_hour(path):
    c = Counter()
    with open(path, encoding="utf-8") as f:      # line by line, without loading it all
        for line in f:
            if " ERROR " not in line:
                continue
            hour = line[11:13]                   # 2024-08-31 14:20:11 ERROR ...
            c[hour] += 1
    return dict(sorted(c.items()))

print(error_by_hour("app.log"))

An important point to make: I read the file line by line, not with a full read() — because a log file may be several gigabytes. The peak at 14:00 is also where you'd start investigating the incident.

import re, unicodedata

FA_DIGITS = str.maketrans("۰۱۲۳۴۵۶۷۸۹٠١٢٣٤٥٦٧٨٩", "01234567890123456789")

def clean_text(s):
    if s is None: return ""
    s = unicodedata.normalize("NFKC", str(s))
    s = s.translate(FA_DIGITS)              # Persian/Arabic digits to Latin
    s = s.replace("ي", "ی").replace("ك", "ک")   # Arabic letters to Persian
    s = re.sub(r"\s+", " ", s).strip()      # extra whitespace
    return s

print(repr(clean_text("  كتاب   ۱۲۳ ")))

If the company works with Persian text data, this function is gold: unifying the Arabic and Persian forms of “ی” and “ک”, Persian digits, zero-width non-joiners and extra spaces. Bringing it up shows you've dealt with real-world Persian data.

def flatten_dict(d, parent="", sep="."):
    out = {}
    for k, v in d.items():
        key = f"{parent}{sep}{k}" if parent else k
        if isinstance(v, dict):
            out.update(flatten_dict(v, key, sep))
        else:
            out[key] = v
    return out

print(flatten_dict({"user": {"name": "Ali", "addr": {"city": "Tehran"}}, "amt": 100}))

Exactly what pd.json_normalize does. If you say “in practice I'd use json_normalize, but this is the logic”, you come across as both practical and in command of the material.

import pandas as pd

first = (trades.groupby("user_id", as_index=False)["trade_time"]
               .min().rename(columns={"trade_time": "first_trade_at"}))

tx = txns.merge(first, on="user_id", how="right")     # right, so users without transactions stay
before = tx[tx["txn_time"] < tx["first_trade_at"]]

agg = (before.pivot_table(index=["user_id", "first_trade_at"],
                          columns="txn_type", values="amount",
                          aggfunc="sum", fill_value=0)
             .reset_index()
             .rename(columns={"deposit": "deposit_before",
                              "withdrawal": "withdraw_before"}))

out = first.merge(agg, on=["user_id", "first_trade_at"], how="left").fillna(0)
out["net_funding"] = out["deposit_before"] - out["withdraw_before"]
print(out.sort_values("net_funding", ascending=False).head(3))

Note that how="right" and then how="left" are deliberate: at both steps we don't want to drop a user with no prior transactions. This is the same INNER JOIN trap from the SQL version, appearing in a different form.

def longest_unique(s):
    last = {}          # character -> last index seen
    left = best = start = 0
    for right, ch in enumerate(s):
        if ch in last and last[ch] >= left:
            left = last[ch] + 1        # move the window past the previous repeat
        last[ch] = right
        if right - left + 1 > best:
            best, start = right - left + 1, left
    return best, s[start:start + best]

print(longest_unique("abcabcbb"))
print(longest_unique("pwwkew"))

The same sliding-window pattern as the leave problem, with a different condition. The last[ch] >= left check is essential: without it, a character seen outside the current window also makes the left pointer jump incorrectly.

def max_subarray(nums):
    best = cur = nums[0]
    start = end = tmp = 0
    for i in range(1, len(nums)):
        if cur < 0:
            cur, tmp = nums[i], i      # fresh start
        else:
            cur += nums[i]
        if cur > best:
            best, start, end = cur, tmp, i
    return best, nums[start:end + 1]

print(max_subarray([-2, 1, -3, 4, -1, 2, 1, -5, 4]))

The intuition: if the running sum so far goes negative, continuing with it never helps, so start over from the next element. A real use is finding the worst or best continuous period of profit and loss in financial data.

import bisect

def lower_bound(arr, target):
    lo, hi = 0, len(arr)
    while lo < hi:
        mid = (lo + hi) // 2
        if arr[mid] < target:
            lo = mid + 1
        else:
            hi = mid
    return lo

a = [1, 3, 3, 5, 8, 13]
print(lower_bound(a, 3), bisect.bisect_left(a, 3))
print(lower_bound(a, 6), bisect.bisect_left(a, 6))

Two points: in Python always use bisect, but you should be able to write it by hand too. And make the loop condition lo < hi with hi = mid, not lo <= hi; this version is less prone to infinite loops and off-by-one errors.

def is_balanced(s):
    pairs = {")": "(", "]": "[", "}": "{"}
    stack = []
    for ch in s:
        if ch in "([{":
            stack.append(ch)
        elif ch in pairs:
            if not stack or stack.pop() != pairs[ch]:
                return False
    return not stack

print(is_balanced("SELECT (a, [b, {c}])"))
print(is_balanced("SELECT (a, [b)]"))

An edge case many people forget: the final not stack check. The string "(((" produces no error inside the loop and, without this line, is wrongly reported as balanced.

from collections import Counter
import heapq

def top_k(nums, k):
    counts = Counter(nums)
    return heapq.nlargest(k, counts.items(), key=lambda kv: kv[1])

print(top_k(["a","b","a","c","b","a"], 2))
print(Counter(["a","b","a","c","b","a"]).most_common(2))

A full sort is O(n log n), but a heap of size k brings the problem down to O(n log k), which makes a real difference when k is much smaller than n. If k is close to n, a plain sort is better — state exactly this trade-off in the interview.

from functools import lru_cache

@lru_cache(maxsize=None)
def fib_memo(n):
    return n if n < 2 else fib_memo(n-1) + fib_memo(n-2)

def fib_iter(n):
    a, b = 0, 1
    for _ in range(n):
        a, b = b, a + b
    return a

print(fib_memo(60), fib_iter(60))

The recursive version without memoization is exponential and practically never finishes for n=50. With lru_cache it becomes linear but uses memory and hits Python's recursion limit at large depths. The iterative version is better on both counts; this comparison is the essence of dynamic programming.

def nth_highest(s, n):
    vals = s.dropna().drop_duplicates().sort_values(ascending=False)
    return vals.iloc[n - 1] if len(vals) >= n else None

print(nth_highest(df["salary"], 3))
print(df["salary"].nlargest(3).iloc[-1])          # without removing duplicates
print(df["salary"].rank(method="dense", ascending=False).eq(3).any())

Explain the difference between the first and second lines: nlargest counts duplicate values separately, so if two people share the highest salary the result differs. The pandas equivalent of DENSE_RANK is rank(method="dense").

rev = (df.groupby(["category", "product_name"], as_index=False)["revenue"].sum())

# Approach 1: sort and groupby.head — simple and fast
top3 = (rev.sort_values(["category", "revenue"], ascending=[True, False])
           .groupby("category").head(3))

# Approach 2: with rank, when you also want the rank number
rev["rn"] = rev.groupby("category")["revenue"].rank(method="first", ascending=False)
top3b = rev[rev["rn"] <= 3].sort_values(["category", "rn"])
print(top3b.head(6))

The method parameter of rank maps exactly onto the choice between SQL window functions: "first" is like ROW_NUMBER, "min" like RANK and "dense" like DENSE_RANK.

daily = (df[df.status == "paid"]
           .groupby(df.order_date.dt.date)["amount"].sum()
           .rename("daily").to_frame())

daily["cumulative"] = daily["daily"].cumsum()

# Cumulative within each month, with a reset
daily.index = pd.to_datetime(daily.index)
daily["mtd"] = daily.groupby(daily.index.to_period("M"))["daily"].cumsum()
print(daily.tail(3))

The groupby(...).cumsum() pattern is the direct equivalent of SUM(...) OVER (PARTITION BY ... ORDER BY ...). For a cumulative sum within a group, always sort the data by time first, because pandas uses the current row order, not the index.

ts = (daily["daily"]
        .asfreq("D", fill_value=0))          # days without sales become zero

out = pd.DataFrame({
    "rev":  ts,
    "ma7":  ts.rolling(7,  min_periods=1).mean().round(),
    "ma30": ts.rolling(30, min_periods=1).mean().round(),
    "ma7_strict": ts.rolling(7).mean().round(),   # the start of the range stays NaN
    "ewm":  ts.ewm(span=7, adjust=False).mean().round(),
})
print(out.head(4))

Explain the difference between min_periods=1 and the default: the first gives numbers from day one, but they are based on an incomplete window and are misleading. In an official report the strict version is usually more correct. ewm is an exponential average that gives more weight to recent data.

# Approach 1: sort and drop_duplicates — the most readable
latest = (df.sort_values(["customer_id", "order_date", "order_id"])
            .drop_duplicates(subset="customer_id", keep="last"))

# Approach 2: idxmax — when a single column decides
latest2 = df.loc[df.groupby("customer_id")["order_date"].idxmax()]

# Approach 3: with rank, when you have several tie-breakers
r = df.sort_values(["order_date", "order_id"], ascending=False)
latest3 = r[r.groupby("customer_id").cumcount() == 0]
print(latest[["customer_id", "order_id", "order_date", "amount"]].head(3))

before = len(df)

clean = (df.sort_values(["email", "created_at"])
           .drop_duplicates(subset="email", keep="last")     # keep the newest
           .reset_index(drop=True))

removed = before - len(clean)
print(f"{removed} rows removed ({removed/before:.2%})")

# Report what was removed before throwing it away
dropped = df.loc[~df.index.isin(clean.index)]
dropped.to_csv("removed_duplicates.csv", index=False, encoding="utf-8-sig")

# Normalize before comparing, otherwise " Ali@X.com " isn't detected as a duplicate
df["email_key"] = df["email"].str.strip().str.lower()

Two good habits: always normalize the comparison key before removing (whitespace, letter case, Persian and Arabic character variants), and save the removed rows. encoding="utf-8-sig" is there so Excel opens non-ASCII text correctly.

# a) Customers without orders
no_orders = customers[~customers.customer_id.isin(orders.customer_id)]

# b) Days without sales
cal = pd.date_range("2024-08-01", "2024-08-31", freq="D")
missing_days = cal.difference(pd.to_datetime(orders.order_date.dt.date.unique()))

# c) Gaps in an id sequence
ids = invoices["id"].sort_values()
gaps = ids[ids.diff() > 1]
for end in gaps:
    prev = ids[ids < end].max()
    print(f"Gap: {prev+1} to {end-1}  ({end-prev-1} records)")

# d) Two-way differences between two sources
cmp = src.merge(tgt, on="id", how="outer", indicator=True)
print(cmp["_merge"].value_counts())

The indicator=True argument is the direct equivalent of FULL JOIN ... IS NULL in SQL and is unbeatable for pipeline reconciliation: one line tells you how many records are unmatched on each side.

import numpy as np

def mad_outliers(s, thresh=3.5):
    """Modified Z method: robust to the outliers themselves."""
    med = s.median()
    mad = (s - med).abs().median()
    if mad == 0:
        return pd.Series(False, index=s.index)
    z = 0.6745 * (s - med) / mad
    return z.abs() > thresh

flag = mad_outliers(df["amount"])
print(f"{flag.sum()} outliers ({flag.mean():.2%})")
print(df.loc[flag, "amount"].describe()[["min", "max"]])

The advantage over a regular Z-score: the mean and standard deviation are themselves affected by outliers, but the median and MAD are not. On skewed data such as purchase amounts, the difference in results is dramatic.

=INDEX(Data[Amount], MATCH(A2, Data[OrderID], 0))

Two-dimensional lookup (row and column):
=INDEX($B$2:$F$50, MATCH($A2, $A$2:$A$50, 0), MATCH(B$1, $B$1:$F$1, 0))

Organizations still on Excel 2016 don't have XLOOKUP. Also, INDEX/MATCH reads only the column it needs, so on large files it is lighter than a VLOOKUP over the whole table.

• Rows: city — Columns: month — Values: sum of amount — Filters: order status.
• Value Field Settings to change the aggregate function and the number format.
• Show Values As → % of Column Total for each city's share.
• Grouping dates into months and quarters by right-clicking the date field.
• Slicers and Timelines for interactive filtering, and connecting several pivots to one filter with Report Connections.

Weighted sum: price × quantity
=SUMPRODUCT(Qty, Price)

Conditions that SUMIFS can't handle:
=SUMPRODUCT((MONTH(Date)=8)*(City="Tehran")*Amount)

Counting unique rows with a condition:
=SUMPRODUCT((City="Tehran")/COUNTIFS(Cust, Cust&"", City, "Tehran"))

SUMPRODUCT can put conditions on the results of functions (like MONTH), whereas SUMIFS only works on the raw column values. The cost is that it gets slow on very large data.

Sorted list of unique cities:
=SORT(UNIQUE(Sales[City]))

Sales for each city next to it:
=SUMIFS(Sales[Amount], Sales[City], H2#)

The ten largest orders of the current month:
=TAKE(SORT(FILTER(Sales, (Sales[Month]=$B$1)*(Sales[Status]="paid")), 4, -1), 10)

The # after an address (as in H2#) means “the whole spilled range”. With it, your report grows and shrinks automatically as the data changes, and you no longer need to drag formulas again.

Drop-down list from a dynamic range:
Data → Validation → List → =CityList

Dynamic name definition in Name Manager:
CityList  =SORT(UNIQUE(Sales[City]))

Dependent conditional formula:
=IF(COUNTIF(CityList, A2)=0, "Invalid city", "")

Manual input is the biggest source of dirty data. Drop-down lists and validation move cleaning from the end of the process to its start.

Formula-based rule, applied to the whole row:
=AND($D2>10000000, $E2="pending")

Highlighting overdue items:
=AND($F2<TODAY(), $G2<>"paid")

The key point: lock the column with $ but leave the row free so the rule applies correctly across the whole row. If you lock both, every row is colored based on a single cell.

=EOMONTH(A2, 0)                 end of the current month
=EOMONTH(A2, -1)+1              start of the current month
=EDATE(A2, 3)                   three months later
=NETWORKDAYS(A2, B2, Holidays)  working days between two dates
=WORKDAY(A2, 5, Holidays)       five working days later
=DATEDIF(A2, TODAY(), "m")      difference in months

Local public holidays (Iran's, for example) don't match the default calendar; you need to put the list of holidays in a named range and pass it as the third argument.

1. Reduce volatile functions (NOW, TODAY, OFFSET, INDIRECT, RAND); they recalculate the whole sheet on every change.
2. Replace whole-column references (A:A) with a table or an exact range.
3. Replace heavy repeated formulas with a single computed helper column.
4. Paste Values for static data.
5. Limit conditional formatting on huge ranges.
6. If the data source is large, move the processing to Power Query and the aggregation to the data model.

The last point is the senior-level answer: Excel is a calculation tool, not a database. Moving heavy logic to Power Query or Power Pivot improves both speed and maintainability.

• Goal Seek: finds the value of one input that makes the output hit a target number.
• Data Table: shows the output's sensitivity to one or two variables as a table.
• Scenario Manager: saves and compares several sets of assumptions (pessimistic, base, optimistic).
• Solver: optimization with an objective function and constraints; for allocation problems and linear programming.

If you have a background in optimization or operations research, Solver is a good bridge: you can say you've solved the same problem at larger scale with Python and optimization libraries.

Reference to a sheet chosen in a cell:
=SUM(INDIRECT("'"&$B$1&"'!C:C"))

Dynamic range based on the number of rows:
=OFFSET($A$1, 1, 0, COUNTA($A:$A)-1, 1)

The modern, better alternative:
=SUM(Sales[Amount])       Excel table, grows automatically

1) Normalize the match key
=TRIM(LOWER(SUBSTITUTE(SUBSTITUTE(A2,"ي","ی"),"ك","ک")))

2) In each file, check whether the key exists in the other file
=IF(COUNTIF(FileB!$A:$A, $A2)=0, "Only in A", "In both")

3) Find value differences
=LET(v, XLOOKUP($A2, FileB!$A:$A, FileB!$D:$D, ""),
     IF(v="", "missing", IF(ABS(v-$D2)<1, "same", v-$D2)))

4) Summary of the result
=COUNTIF($F:$F,"Only in A") & " unmatched records"

Never skip the first step. In Persian data, extra spaces and the Arabic forms of “ی” and “ک” account for most of the “no match” cases between records that are actually the same.

1. Select all cells and untick Locked in Format Cells.
2. Select only the cells with formulas and lock them again.
3. On the Review tab, apply Protect Sheet with a password and allow selecting locked cells.
4. Hide sensitive formulas with the Hidden option so they don't show in the formula bar.
5. Lock the file structure against deleting or renaming sheets with Protect Workbook.

Inputs (blue cells, separate from the calculations):
  GrowthRate   B2   3%
  Churn        B3   5%
  ARPU         B4   250,000

Calculation, column by column for each month:
Customers:  =ROUND(D5*(1+$B$2)*(1-$B$3), 0)
Revenue:    =D6*$B$4
CumCash:    =C8+D7-D9

Sensitivity analysis with a Data Table:
Data → What-If Analysis → Data Table
Row: growth rate    Column: churn rate    Result cell: first-year profit

The golden rule of financial modeling: never write a constant inside a formula. All assumptions should sit in a clearly marked, colored input area so anyone can change the scenario without touching the formulas.

Composite mode lets some tables be Import and some DirectQuery. Dual mode is used for dimension tables so they take on the right role depending on the query.

The right default answer: “Import, unless we have a real need for real-time data or a volume beyond capacity.”

In single direction, the filter flows from the “one” side to the “many” side; selecting a customer filters their sales, but not the other way round.

Bidirectional lets the filter flow back. Its problems:

• Ambiguous filter paths in multi-table models and ambiguity errors.
• Unintuitive results that are hard to debug.
• A noticeable drop in performance.
• Combined with RLS, it can leak data.

-- Instead of making it permanently bidirectional, case by case and under control:
Customers With Sales =
CALCULATE(
    DISTINCTCOUNT(Customer[CustomerID]),
    CROSSFILTER(Sales[CustomerID], Customer[CustomerID], BOTH)
)

This answer (“single direction by default, bidirectional only temporarily inside CALCULATE”) is a sign of mature modeling.

Date =
VAR MinD = MIN(Sales[OrderDate])
VAR MaxD = MAX(Sales[OrderDate])
RETURN
ADDCOLUMNS(
    CALENDAR(DATE(YEAR(MinD),1,1), DATE(YEAR(MaxD),12,31)),
    "Year",      YEAR([Date]),
    "MonthNo",   MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "Quarter",   "Q" & QUARTER([Date]),
    "YearMonth", FORMAT([Date], "YYYY-MM"),
    "IsWorkday", WEEKDAY([Date], 2) < 6
)

Then right-click, choose Mark as Date Table, and sort the MonthName column by MonthNo (Sort by Column).

-- In Modeling → Manage Roles, on the Customer table:
[Region] = LOOKUPVALUE(
    UserRegion[Region],
    UserRegion[Email], USERPRINCIPALNAME()
)

-- Or simpler, for a fixed role:
[Region] = "Tehran"

In Desktop you test with View as Role, and in the Service you assign users to roles.

• Static RLS: one role per group; simple, but unmaintainable as the number of groups grows.
• Dynamic RLS: a single role that reads access from a mapping table using the current user's email.

1. In Power Query, create two date/time parameters named exactly RangeStart and RangeEnd.
2. Filter the fact table's date column with these two parameters.
3. Enable incremental refresh on the table: for example “archive 5 years, refresh 10 days”.
4. After publishing, the service partitions the table and reads only the recent partitions.

Its success depends on query folding: the filter must be pushed down to the source, otherwise all the data is read and then filtered, and you gain nothing. Be sure to mention this condition.

1. Turn on Performance Analyzer and see which visual is slow, and how much of it is DAX query versus visual display.
2. Run the DAX query in DAX Studio and look at the ratio of Formula Engine to Storage Engine time; a high FE share means iterative, row-by-row DAX.
3. Simplify the model: remove unused columns, reduce cardinality (for example by splitting date and time), remove heavy calculated columns.
4. Reduce the number of visuals per page; each visual is a separate query.
5. Limit page- and report-level filters and use sensible defaults.
6. For large models, define aggregation tables.

Mentioning the split between the Formula Engine and the Storage Engine is a sign of real optimization experience. Most candidates only get as far as Performance Analyzer.

• Drillthrough: the user right-clicks a city and goes to that city's details page; the filter context is carried over.
• Bookmark: saves a state (filters, visible visuals) for building a narrative or a button that switches charts.
• Tooltip page: a small page that shows details when the mouse hovers over a point on a chart.
• Drill down: moving through the year → quarter → month hierarchy within one visual.

Combining bookmarks with the Selection Pane to build a “show as table / chart” button is common and looks good in a portfolio.

A dataflow is Power Query that runs in the service and stores its output, so several reports can use one shared transformation logic.

• The cleaning logic is written once and used everywhere.
• Load on the source drops; it is read once instead of five times.
• A single definition of reference tables (customer, product, calendar) across the organization.

If they ask how it differs from a semantic model: a dataflow is the data preparation layer, a semantic model is the modeling and calculation layer.

• A workspace is the builders' working area, with Admin, Member, Contributor and Viewer roles.
• An app is a published, organized package for end consumers; end users usually see the app, not the workspace.
• To view shared content, both sides need a Pro license; unless the content is in Premium/Fabric capacity, in which case consumers can have a free license.

The standard deployment flow: Dev → Test → Prod with a deployment pipeline. Saying this shows you think about the report's life cycle, not just building it.

It lets users switch a visual's metric or dimension themselves, without you building several separate visuals.

-- Modeling → New parameter → Fields
Metrics = {
    ("Revenue",    NAMEOF('_Measures'[Total Sales]),  0),
    ("Orders",     NAMEOF('_Measures'[Total Orders]), 1),
    ("Customers",  NAMEOF('_Measures'[Customers]),    2),
    ("Avg basket", NAMEOF('_Measures'[AOV]),          3)
}

Then put the generated table in a slicer and the parameter itself on the visual's axis.

This way one page replaces five repetitive pages, and maintaining the report becomes much simpler. You can build the same thing for switching dimensions (city, product, channel).

• Mobile layout in Desktop: a separate vertical layout with the key visuals.
• Set the tab order in the Selection Pane so a screen reader moves through it logically.
• Write alt text for every visual.
• Sufficient contrast, and don't convey meaning through color alone; use markers and labels too.
• Don't use a font size below 10, and avoid rotated labels.

A significant share of users open reports on their phones. If you don't define a mobile layout, the whole wide page is shrunk down and becomes practically unusable.

A histogram for the overall shape of the distribution and a box plot for comparing distributions between groups.

-- Creating bins in Power BI: on a numeric column → New group → Bin
-- Or a calculated column for custom bins
Amount Band =
SWITCH ( TRUE (),
    Sales[Amount] < 100000,  "Under 100K",
    Sales[Amount] < 300000,  "100K to 300K",
    Sales[Amount] < 1000000, "300K to 1M",
    "Over 1M" )

An average alone hides the distribution. If a manager only sees the average basket size, they may not know that eighty percent of orders are very small and a few large orders pulled the average up. A histogram shows this at a glance.

A funnel chart for display, together with a small table giving each step's conversion rate.

The funnel chart itself is pretty but doesn't state the important numbers. The conversion rate of “each step relative to the previous one” is what drives decisions — and the default funnel chart doesn't show it. Always add it as labels or a table next to it.

A matrix with conditional formatting (a retention triangle): cohorts in rows, month number in columns, the retention percentage in each cell on a color scale.

Three things can be read from this matrix: the first-step drop, the curve flattening in later months, and a vertical comparison of cohorts.

A good complement is a line chart with one line per cohort and “months since start” on the horizontal axis — this version shows more clearly whether the curve flattens.

• A filled map for comparing provinces, a bubble map for absolute values at points.
• In Power BI, set the column's Data Category correctly (Province, City), otherwise points land in the wrong place.
• Place names in local languages are sometimes not recognized correctly; add latitude and longitude columns or a standard code.
• For a filled map, normalize the value: sales per capita, not total sales.

Three options, in order of preference:

1. An actual bar with a target line on the same axis (a combo chart with one column and one line).
2. A bullet chart, which shows the value, the target and the performance ranges in little space.
3. A variance chart: show only the difference, positive in green and negative in red.

Two bars side by side (actual and budget) is the most common choice but the worst: the eye has to calculate the difference. A variance chart has already done that work and delivers the message instantly.

• When the user needs the exact number, not a pattern — for example a financial report that has to be reconciled with the accounting system.
• When several unrelated metrics are needed for each row.
• When the output is going to be exported to Excel.
• When there are so many categories that any chart gets cluttered.

For tables: conditional formatting with data bars, a meaningful default sort, right-aligned numeric columns, and a frozen header.

A table plus conditional formatting is often the best combination: it has numeric precision and shows the pattern with color. Underestimating it is a common mistake of beginner designers.

• Filter context: the set of active filters — from the visual's rows and columns, slicers, page filters and CALCULATE arguments. Measures are evaluated in this context.
• Row context: the “current row” while computing a calculated column or inside an iterator. On its own it applies no filter to the model.

-- Row context: has access to the current row
Line Total = Sales[Qty] * Sales[Price]

-- Filter context: affected by the visual and slicers
Total = SUM(Sales[Amount])

A common fallacy: assuming a calculated column also sees the visual's filters. A column is computed and stored when the data is loaded, so it knows nothing about the user's selections.

It is the only function that can change the filter context: add a filter, remove one or replace one.

-- New filter; an existing filter on the same column is overwritten
Sales Tehran = CALCULATE([Total Sales], Customer[City] = "Tehran")

-- Remove a filter
Sales All Cities = CALCULATE([Total Sales], ALL(Customer[City]))

-- Add without overwriting
Sales Big Tehran =
CALCULATE([Total Sales], KEEPFILTERS(Customer[City] = "Tehran"))

Explain the difference between the third and fourth columns: without KEEPFILTERS the city filter is overwritten, so in the “Mashhad” row you also see Tehran's number; with KEEPFILTERS the intersection of the two filters is taken and the result is blank.

When CALCULATE runs in the presence of a row context, that row is turned into a filter. Every measure call is also implicitly wrapped in CALCULATE, so the same thing happens.

-- Without transition: sees the whole table, the same number for everyone
Wrong = SUMX(Customer, [Total Sales])   -- transition actually does happen here

-- Explicit and clear
Customer Rank Base =
SUMX(
    VALUES(Customer[CustomerID]),
    CALCULATE([Total Sales])            -- the current row becomes a filter
)

This concept is exactly what separates senior-level DAX questions from intermediate ones. If you can explain it with a simple example, your command of the language is obvious.

-- Share of the absolute total (ignoring slicers)
% of Grand Total =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales)))

-- Share of the user's selected total
% of Selected =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED()))

-- Share of that city's total
% of City =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALLEXCEPT(Customer, Customer[City])))

ALLSELECTED removes the filters inside the visual but keeps the outer filters (slicers and page). For “percentage of what the user selected”, this is always the right one.

YoY % =
VAR CurrentSales  = [Total Sales]
VAR PriorSales    = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
VAR Growth        = CurrentSales - PriorSales
RETURN
    IF(
        NOT ISBLANK(PriorSales) && PriorSales <> 0,
        DIVIDE(Growth, PriorSales)
    )

• Each variable is computed once, even if it is used several times.
• A variable is evaluated in the context where it is defined and doesn't change afterwards — this behavior makes debugging simpler.
• The code becomes readable and testable: you can temporarily point RETURN at one variable and see its value.

Mention that same debugging technique (pointing RETURN at an intermediate variable) in the interview; it shows you've really written complex DAX.

Sales PY  = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Sales PM  = CALCULATE([Total Sales], PREVIOUSMONTH('Date'[Date]))
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])

-- A more flexible version with DATEADD
Sales LY Flexible = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, YEAR))

YoY %  = DIVIDE([Total Sales] - [Sales PY], [Sales PY])

For a non-calendar fiscal year, pass the year-end argument: TOTALYTD([Total Sales], 'Date'[Date], "03-20") (the Iranian fiscal year, for example). In such environments this point is practical and sets you apart.

Running Total =
VAR LastDate = MAX('Date'[Date])
RETURN
CALCULATE(
    [Total Sales],
    'Date'[Date] <= LastDate,
    ALL('Date')
)

Two points: MAX takes the last date of the current context, and ALL('Date') removes the current month's filter so we can look backwards. If you remove ALL, the result equals the month itself.

Product Rank =
IF(
    HASONEVALUE(Product[ProductName]),
    RANKX(
        ALLSELECTED(Product[ProductName]),
        [Total Sales],
        ,
        DESC,
        DENSE
    )
)

The HASONEVALUE condition prevents a meaningless rank from showing in the grand-total row. Using ALLSELECTED keeps the ranking in step with the user's selection.

-- In the Sales table (many side): brings one value from the dimension
Customer City = RELATED(Customer[City])

-- In the Customer table (one side): brings the table of related rows
Order Count = COUNTROWS(RELATEDTABLE(Sales))

A simple rule: from the “many” side to the “one” side with RELATED, and from the “one” side to the “many” side with RELATEDTABLE. Both need an active relationship.

-- Dynamic title for a visual
Dynamic Title =
"Sales in " & SELECTEDVALUE(Customer[City], "all cities")
      & ", " & SELECTEDVALUE('Date'[Year], "all years")

-- Protecting the calculation in the grand-total row
Unit Price =
IF(HASONEVALUE(Product[ProductID]), DIVIDE([Revenue], [Quantity]))

Wherever a calculation only makes sense for a single item (unit price, rank, rate), guard it with HASONEVALUE so the total rows don't produce misleading numbers.

-- Stock on the last day with data in the selected range
Closing Stock =
CALCULATE (
    SUM ( Inventory[OnHandQty] ),
    LASTNONBLANK ( 'Date'[Date], CALCULATE ( COUNTROWS ( Inventory ) ) )
)

Opening Stock =
CALCULATE (
    SUM ( Inventory[OnHandQty] ),
    FIRSTNONBLANK ( 'Date'[Date], CALCULATE ( COUNTROWS ( Inventory ) ) )
)

Summing inventory over time is meaningless; you have to take the last value. LASTNONBLANK is preferable to LASTDATE, because if the last day has no data it steps back instead of returning a blank row.

-- What-if parameter: Discount % from 0 to 30 in steps of 1
Simulated Revenue =
VAR d = SELECTEDVALUE ( 'Discount %'[Discount %], 0 )
VAR uplift = 1 + d * 0.03          -- assumption: each 1% discount gives 3% volume growth
RETURN
SUMX ( Sales, Sales[Qty] * uplift * Sales[UnitPrice] * ( 1 - d ) )

Margin Impact = [Simulated Revenue] - [Total Sales]

A what-if parameter creates a standalone numeric table that isn't connected to the model, so its filter doesn't affect the data and it is read only with SELECTEDVALUE. A good tool for interactive scenario building in management meetings.

-- When the calendar is Solar Hijri or fiscal and the built-in Gregorian functions don't work
Sales PY Custom =
VAR CurrIdx = SELECTEDVALUE ( 'Date'[MonthIndex] )   -- continuous month counter
RETURN
CALCULATE (
    [Total Sales],
    REMOVEFILTERS ( 'Date' ),
    'Date'[MonthIndex] = CurrIdx - 12
)

YoY % = DIVIDE ( [Total Sales] - [Sales PY Custom], [Sales PY Custom] )

The “continuous counter” trick is very practical: an integer column that numbers the months consecutively from the start of the data. With it, a time shift becomes a simple subtraction and you escape the calendar's complexity — especially for the Solar Hijri calendar, which DAX's built-in functions don't support.

EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Year],
    Customer[City],
    FILTER ( ALL ( 'Date'[Year] ), 'Date'[Year] >= 2023 ),
    "Sales",     [Total Sales],
    "Orders",    [Total Orders],
    "AOV",       DIVIDE ( [Total Sales], [Total Orders] )
)
ORDER BY [Sales] DESC

This function is what Power BI itself generates behind every visual. Unlike SUMMARIZE, which is problematic for building calculated columns, this one is optimized and automatically drops combinations without data.

Cumulative Sales =
VAR CurrSales = [Total Sales]
VAR Visible = ALLSELECTED ( Product[ProductName] )
VAR WithSales =
    ADDCOLUMNS ( Visible, "@s", CALCULATE ( [Total Sales] ) )
RETURN
    SUMX ( FILTER ( WithSales, [@s] >= CurrSales ), [@s] )

Cumulative % =
DIVIDE ( [Cumulative Sales], CALCULATE ( [Total Sales], ALLSELECTED () ) )

This pattern is “accumulation by rank”, not by time: for each row, the total of all rows whose value is greater than or equal is computed. With it you can easily show how many products make up eighty percent of revenue.

Power Query tries to translate your steps into the source's language (for example SQL) and hand the processing to the server. If it succeeds, only the final result is transferred.

// Folds: translated into WHERE and GROUP BY in SQL
Table.SelectRows(Source, each [Date] >= #date(2024,1,1))

// Breaks folding: custom logic with no SQL equivalent
Table.AddColumn(Source, "X", each MyCustomFunction([A]))

Right-click a step and choose View Native Query to see how far it has folded. If the option is grayed out, folding is broken.

// Input: City | Jan | Feb | Mar   (report layout)
Unpivoted = Table.UnpivotOtherColumns(Source, {"City"}, "Month", "Amount"),
Typed     = Table.TransformColumnTypes(Unpivoted, {{"Amount", type number}})

Always prefer UnpivotOtherColumns to UnpivotColumns: if a new month is added to the source file, the first one automatically picks it up too, but the second breaks.

Grouped = Table.Group(Source, {"City", "Month"}, {
    {"Revenue",   each List.Sum([Amount]),            type number},
    {"Orders",    each Table.RowCount(_),             Int64.Type},
    {"Customers", each List.Count(List.Distinct([customer_id])), Int64.Type},
    {"MaxOrder",  each List.Max([Amount]),            type number}
})

The _ symbol refers to the whole group table and [Amount] to one of its columns. This distinction is the source of most errors when writing Table.Group by hand.

let
    Source   = Folder.Files("C:\reports\monthly"),
    OnlyCsv  = Table.SelectRows(Source, each Text.EndsWith([Name], ".csv")),
    Parsed   = Table.AddColumn(OnlyCsv, "Data",
                   each Table.PromoteHeaders(
                        Csv.Document([Content], [Encoding=65001]))),
    Selected = Table.SelectColumns(Parsed, {"Name", "Data"}),
    Expanded = Table.ExpandTableColumn(Selected, "Data",
                   {"order_id", "amount", "city"})
in
    Expanded

Keeping the Name column is a good trick: later you can tell which file each row came from, which is vital for troubleshooting inconsistent data.

// Replace the erroneous value
Safe = Table.AddColumn(Source, "Amount_clean",
           each try Number.From([amount_text]) otherwise null, type number),

// Keep the error message for review
Diag = Table.AddColumn(Source, "Err",
           each try Number.From([amount_text]),
       [ErrorMessage = if [Err][HasError] then [Err][Error][Message] else null]),

// Remove rows with errors
Clean = Table.RemoveRowsWithErrors(Source, {"amount"})

Before removing, use “Keep Errors” and look at the rows with errors. There is often a clear pattern, such as a thousands separator or Persian digits, which should be fixed at the root rather than by deleting.

// Parameters: ServerName, DatabaseName, RangeStart, RangeEnd
let
    Source = Sql.Database(ServerName, DatabaseName),
    Sales  = Source{[Schema="dbo", Item="Sales"]}[Data],
    Filtered = Table.SelectRows(Sales,
        each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)
in
    Filtered

Uses: switching between development and production environments without editing the query, a configurable file path, and a prerequisite for incremental refresh (which needs exactly two parameters with these names).

Missing = Table.NestedJoin(Orders, {"customer_id"}, Customers, {"customer_id"},
                           "m", JoinKind.LeftAnti)

The two anti joins have no direct equivalent in SQL and must be written with NOT EXISTS. They are very useful for data quality control: a LeftAnti merge tells you immediately how many orders have unmatched customers.

let
    GetPage = (page as number) as table =>
        let
            raw  = Json.Document(Web.Contents("https://api.example.com/orders",
                        [Query = [page = Text.From(page), per_page = "500"]])),
            rows = Table.FromRecords(raw[items])
        in
            rows,
    First  = Json.Document(Web.Contents("https://api.example.com/orders",
                 [Query = [page = "1", per_page = "500"]])),
    Total  = Number.RoundUp(First[total] / 500),
    Pages  = List.Transform({1..Total}, each GetPage(_)),
    All    = Table.Combine(Pages)
in
    All

Always pass the parameters in the Query record, not by concatenating strings onto the URL; only then does Power BI correctly recognize the base URL for authentication, and the query can also be refreshed in the service.

Each source gets one of three levels: Public, Organizational or Private. Power Query prevents data leaking between sources with incompatible levels.

• If a source is Private, its values can't be used in a query sent to another source.
• The result: the engine is forced to process everything locally, and folding breaks completely.
• The symptom is sudden, unexplained slowness after adding a new source.

If all sources are inside the organization and trustworthy, you can set the levels to Organizational or temporarily disable the privacy check. This is one of the least-known causes of performance drops.

Merged   = Table.NestedJoin(Orders, {"customer_id"}, Customers, {"customer_id"},
                            "cust", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "cust",
              {"name", "city", "segment"},
              {"customer_name", "customer_city", "customer_segment"})

The third argument is the names of the columns inside the nested table and the fourth is the names you want in the output.

• Duplicate makes an independent copy; changes to the original don't affect the copy.
• Reference creates a new query that takes the first query's output as its input; changes to the original affect all of them.

// Base query: Orders_Base  (connection only, not loaded)
let Source = Sql.Database("srv","dwh"),
    T = Source{[Schema="dbo", Item="Orders"]}[Data],
    Clean = Table.SelectRows(T, each [Status] = "paid")
in  Clean

// Derived query: Orders_Monthly
let S = Orders_Base,
    G = Table.Group(S, {"Month"}, {{"Rev", each List.Sum([Amount]), type number}})
in  G

The right pattern: one base query with “Enable load” turned off, and several derived queries that are loaded. This way the cleaning logic is written once and the source is read only once.

-- SCD Type 2
CustomerKey | CustomerID | City   | ValidFrom  | ValidTo    | IsCurrent
------------+------------+--------+------------+------------+----------
       1001 |     C-102  | Tehran | 2023-01-01 | 2024-05-31 | false
       1450 |     C-102  | Karaj  | 2024-06-01 | 9999-12-31 | true

Choosing the type is a business decision, not a technical one. The right question is: “If a customer moves to another city, should last year's sales be attributed to Tehran or to Karaj?” The answer determines the SCD type.

-- Step 1: close the changed rows
UPDATE DimCustomer d
SET    ValidTo = CURRENT_DATE - 1,
       IsCurrent = false
FROM   stg_customers s
WHERE  d.CustomerID = s.CustomerID
  AND  d.IsCurrent
  AND  (d.City, d.Segment) IS DISTINCT FROM (s.City, s.Segment);

-- Step 2: insert a new version for those + new customers
INSERT INTO DimCustomer (CustomerID, Name, City, Segment, ValidFrom, ValidTo, IsCurrent)
SELECT s.CustomerID, s.Name, s.City, s.Segment,
       CURRENT_DATE, DATE '9999-12-31', true
FROM   stg_customers s
LEFT JOIN DimCustomer d
       ON d.CustomerID = s.CustomerID AND d.IsCurrent
WHERE  d.CustomerKey IS NULL;

Using IS DISTINCT FROM is deliberate: with <>, if one of the values is NULL the condition is neither true nor false and the change is silently ignored. Exactly this detail separates a correct SCD implementation from a wrong one.

• Transaction fact — one row per event. The finest-grained and most common.
• Periodic snapshot — a snapshot of the state at the end of each period; inventory, account balance.
• Accumulating snapshot — one row for the entire life cycle of a process, with date columns that fill in over time.
• Factless fact — no numeric measure; records an event or coverage.

-- Accumulating snapshot for the order life cycle
FactOrderCycle(
  OrderKey, CustomerKey,
  OrderDateKey, ApprovedDateKey, ShippedDateKey, DeliveredDateKey,
  DaysToApprove, DaysToShip, DaysToDeliver, OrderAmount
)

Unlike the others, the third type is updated, and it is unbeatable for analyzing cycle times and process bottlenecks. Mentioning it in an interview is rare and earns points.

The fix for non-additive measures: store their components in the fact, not the ratio itself. Keep the numerator and denominator and compute the ratio in the measure layer, otherwise you get the average of percentages instead of the percentage of the total.

A dimension shared by several fact tables with the same definition, keys and values. For example, the same DimDate and DimProduct connected to both sales and inventory.

-- Comparing sales and inventory is only possible thanks to shared dimensions
SELECT d.MonthName, p.Category,
       SUM(f.NetAmount) AS sales,
       AVG(i.OnHandQty)  AS avg_stock
FROM DimDate d
JOIN FactSales f     ON f.DateKey = d.DateKey
JOIN FactInventory i ON i.DateKey = d.DateKey AND i.ProductKey = f.ProductKey
JOIN DimProduct p    ON p.ProductKey = f.ProductKey
GROUP BY 1, 2;

This capability is called “drill across”: moving horizontally between several business processes. Without conformed dimensions, each fact becomes an island and comparisons between them are impossible.

• Degenerate dimension — an identifier such as an invoice or transaction number that has no descriptive attributes, so it needs no separate dimension table and stays directly in the fact.
• Junk dimension — several low-value flags and statuses (was it a gift? payment method, channel), each not worth its own table, combined into one composite dimension.

DimOrderFlags(FlagKey, IsGift, PaymentMethod, Channel, IsFirstOrder)
-- Number of rows = product of the possible states, usually a few dozen rows
FactSales(..., FlagKey, InvoiceNumber /* degenerate */, ...)

Without a junk dimension, the fact table fills up with flag columns and the model becomes cluttered and slow. Few candidates know these two concepts.

Example: a patient receives several diagnoses in one visit, or a product belongs to several categories at once. The solution is a bridge table.

FactVisit(VisitKey, PatientKey, DateKey, DiagnosisGroupKey, Cost)
BrgDiagnosisGroup(DiagnosisGroupKey, DiagnosisKey, Weight)
DimDiagnosis(DiagnosisKey, DiagnosisName, Category)

-- 1) Recording an event: a student attending a class
FactAttendance(DateKey, StudentKey, CourseKey, InstructorKey)

-- 2) Coverage: which product was available in which store
FactProductCoverage(DateKey, StoreKey, ProductKey, PromoKey)

They have no numeric measure, but the presence or absence of a row is meaningful. The second case makes it possible to answer “which products were available but didn't sell” — a question that can never be answered with the sales table alone.

The measure for these tables is always COUNT(*). Sometimes a constant column with the value one is added so BI tools can work with it more easily.

The practical answer: “I usually choose Kimball because it delivers value sooner, but I prevent inconsistency between marts from the start with a bus matrix and conformed dimensions.” This sentence shows you also know the weakness of your chosen approach.

A table that puts business processes in rows and dimensions in columns and shows which dimensions each process uses.

This matrix is both the project roadmap and a guarantee that shared dimensions are built once and correctly. Drawing this very table on the whiteboard is a first-class answer to “where would you start a data warehouse”.

Late-arriving dimension: a fact row arrives but the customer isn't in the dimension table yet. The solution is to create a placeholder row (an inferred member) that is completed later.

INSERT INTO DimCustomer (CustomerID, Name, City, IsInferred, ValidFrom, ValidTo, IsCurrent)
SELECT DISTINCT s.CustomerID, 'Unknown', 'Unknown', true, CURRENT_DATE, '9999-12-31', true
FROM stg_sales s
LEFT JOIN DimCustomer d ON d.CustomerID = s.CustomerID AND d.IsCurrent
WHERE d.CustomerKey IS NULL;

Late-arriving fact: a transaction with a past date arrives. You must find the dimension version matching that transaction's date, not the current version:

JOIN DimCustomer d
  ON d.CustomerID = s.CustomerID
 AND s.TxnDate BETWEEN d.ValidFrom AND d.ValidTo

Using IsCurrent = true during loading is the simplest approach but gives wrong results for late data. A range join is the correct answer.

CREATE MATERIALIZED VIEW AggSalesMonthlyCategory AS
SELECT d.Year, d.Month, p.Category, s.StoreKey,
       SUM(f.NetAmount) AS NetAmount,
       SUM(f.Quantity)  AS Quantity,
       COUNT(DISTINCT f.CustomerKey) AS Buyers
FROM FactSales f
JOIN DimDate d    ON d.DateKey    = f.DateKey
JOIN DimProduct p ON p.ProductKey = f.ProductKey
JOIN DimStore s   ON s.StoreKey   = f.StoreKey
GROUP BY 1,2,3,4;

Rule: build an aggregate table when you've measured that the query really is slow, and always refresh it in the same transaction as the fact load or right after it.

• In OLTP, many indexes on search columns; in a warehouse, fewer but targeted indexes.
• Fact tables are usually partitioned by date so partition pruning happens and removing old data is cheap.
• Indexes on the fact's foreign keys are useful for joining to dimensions.
• In columnar databases, traditional indexes give way to sorting and block-level statistics.

CREATE TABLE FactSales (...) PARTITION BY RANGE (DateKey);
CREATE TABLE FactSales_2024_08 PARTITION OF FactSales
  FOR VALUES FROM (20240801) TO (20240901);
CREATE INDEX ON FactSales_2024_08 (CustomerKey);

During bulk loads, drop the indexes and rebuild them after the insert; on tens of millions of rows it makes a dramatic difference in time.

Like an org chart whose depth differs between branches. Two common solutions:

1. Flattening to a fixed number of levels: Level1 to Level6 columns; simple and fast, but with empty levels and a maximum depth.
2. A hierarchy bridge table: one row for each ancestor–descendant pair, with the level distance.

BrgOrgHierarchy(AncestorKey, DescendantKey, Depth, IsLeaf)
-- Total salary of a manager's entire subtree
SELECT SUM(f.Salary)
FROM BrgOrgHierarchy b
JOIN FactPayroll f ON f.EmployeeKey = b.DescendantKey
WHERE b.AncestorKey = :manager_key;

A bridge table gives full flexibility and supports unlimited depth, but joins become more expensive and BI tools cope with it less easily. For shallow, stable hierarchies, flattening is the more practical choice.

FactSales(
  ...,
  Amount_TXN      numeric(14,2),   -- in the transaction currency
  CurrencyKey     int,
  ExchangeRate    numeric(12,6),   -- the rate on that date
  Amount_REPORT   numeric(14,2)    -- in the reporting currency, precomputed
)

• Store both values: the original currency for accounting accuracy, the reporting currency for aggregation.
• Store the exchange rate as of the transaction date, not today's rate.
• Keep the unit of quantity (each, kilogram, pack) in the product dimension and forbid summing mixed units.

A combination of all three types: Type 2 historical rows, plus a column that always holds the current value.

CustomerKey | CustomerID | City_Historical | City_Current | ValidFrom  | IsCurrent
------------+------------+-----------------+--------------+------------+----------
       1001 |     C-102  | Tehran           | Karaj        | 2023-01-01 | false
       1450 |     C-102  | Karaj            | Karaj        | 2024-06-01 | true

With one model you can ask both “last year's sales by the customer's city at the time” and “last year's sales by the customer's current city” — two completely different questions, both legitimate.

The cost is that the current-value column has to be updated in all of that customer's historical rows, so loading gets heavier. For large dimensions, watch out for this cost.

Always raise the problem of deletes: with updated_at, a record deleted in the source stays in the warehouse. Solutions: soft deletes in the source, periodically comparing the list of keys, or moving to CDC.

Postgres WAL ──► Debezium ──► Kafka topic ──► consumer ──► Delta/warehouse
Sample message: {"op":"u","before":{...},"after":{...},"ts_ms":1725...}

• Inserts, updates and deletes are all captured.
• No load is placed on the operational system's queries, because it reads from the log.
• Very low latency and the ability to replay from an earlier offset.

-- Getting the latest state of each record from the CDC stream
SELECT DISTINCT ON (id) *
FROM cdc_orders
WHERE op <> 'd'
ORDER BY id, ts_ms DESC;

An important point: a CDC stream is ordered per key, not globally. So always pick the latest version by key and timestamp, and don't assume messages arrive in order.

def run(run_date):
    """All logic is driven by the date parameter, not by today."""
    raw   = extract(run_date)
    clean = transform(raw)
    load(clean, partition=run_date)     # drop the partition and re-insert

# Re-run for a past month
for d in pd.date_range("2024-07-01", "2024-07-31"):
    run(d.date())

• Nowhere in the code should today() be called; everything comes from the date parameter.
• Each partition should be independent so they can run in parallel.
• A backfill must not run at the same time as the regular run on the same partition.
• Respect the source's rate limits; a one-year backfill can bring the source system down.

The key sentence: “I write the pipeline so that today's run and a run for six months ago have no difference in code; the only difference is the parameter value.”

good = df[df["amount"].notna() & (df["amount"] >= 0)]
bad  = df.drop(good.index)
if len(bad):
    bad.assign(rejected_at=pd.Timestamp.utcnow(), reason="invalid_amount") \
       .to_parquet(f"s3://lake/_rejected/orders/{run_date}.parquet")
    log.warning("rejected %d rows (%.2f%%)", len(bad), 100*len(bad)/len(df))
if len(bad) / max(len(df), 1) > 0.02:
    raise ValueError("Faulty record rate exceeded the threshold")

Two key points: don't throw bad records away, set them aside so they can be reviewed; and set a threshold — a few faulty records is normal, two percent signals a structural problem.

EXPECTED = {"order_id":"int64","customer_id":"int64",
            "order_date":"datetime64[ns]","amount":"float64","status":"object"}

missing = set(EXPECTED) - set(df.columns)
if missing:
    raise ValueError(f"Columns missing in source: {missing}")

extra = set(df.columns) - set(EXPECTED)
if extra:
    log.warning("New columns ignored: %s", extra)

for c, t in EXPECTED.items():
    if str(df[c].dtype) != t:
        log.warning("Type of %s changed: %s (expected %s)", c, df[c].dtype, t)

A suggested policy: ignore new columns and just warn; treat missing columns or incompatible type changes as stopping errors.

The long-term solution is a data contract: the producer commits not to change the structure without prior notice, and incompatible changes are blocked in CI.

-- Daily comparison of source and target
WITH src AS (
  SELECT order_date::date AS d, COUNT(*) AS rows, SUM(amount) AS amt
  FROM source_orders WHERE order_date::date = :run_date GROUP BY 1
), tgt AS (
  SELECT date_key AS d, COUNT(*) AS rows, SUM(net_amount) AS amt
  FROM fact_sales WHERE date_key = :run_date GROUP BY 1
)
SELECT s.d, s.rows AS src_rows, t.rows AS tgt_rows,
       s.amt AS src_amt, t.amt AS tgt_amt,
       s.rows - t.rows AS row_diff,
       ROUND(s.amt - t.amt, 2) AS amt_diff
FROM src s FULL JOIN tgt t USING (d);

Run this query as the last step of the pipeline, and if there's a difference, stop publishing. This is exactly the difference between a professional pipeline and an amateur one: it notices it's broken itself, instead of a manager noticing.

-- Keep the latest version of each key before inserting
INSERT INTO fact_sales
SELECT * FROM (
  SELECT s.*,
         ROW_NUMBER() OVER (PARTITION BY s.order_id, s.line_no
                            ORDER BY s.updated_at DESC, s.ingest_ts DESC) AS rn
  FROM stg_sales s
) t
WHERE rn = 1
ON CONFLICT (order_id, line_no) DO UPDATE
SET net_amount = EXCLUDED.net_amount,
    updated_at = EXCLUDED.updated_at
WHERE fact_sales.updated_at < EXCLUDED.updated_at;

The final condition matters: it prevents a newer record from being overwritten by an older version. When processing out-of-order messages — which is normal in CDC and Kafka — this protection is essential.

• A database sequence — simple and compact, but a bottleneck in parallel or distributed loads.
• A hash of the business key — can be computed in parallel with no central coordination; the common approach in Data Vault.
• UUID — collision-free but bulky and slow for joins.

-- Hash key, stable and reproducible
SELECT md5(coalesce(source_system,'') || '|' || coalesce(customer_id,'')) AS customer_hk
FROM stg_customers;

The advantage of a hash key is that if you run the pipeline from scratch, the same keys are produced and joins don't break. With a database sequence, a re-run produces different keys and the whole warehouse has to be rebuilt.

extract_orders    >> transform_orders
extract_customers >> transform_customers
[transform_orders, transform_customers] >> build_fact >> quality_checks >> publish

• Dimensions should load before facts, otherwise orphan keys are produced.
• Independent tasks should run in parallel to keep the batch window short.
• Quality checks should come before publishing, not after.
• The graph must have no cycles; the orchestrator checks this itself.
• For a dependency on another pipeline, use a sensor or an event, not a fixed delay.

The last point matters: “wait 30 minutes, then start” is the most fragile pattern there is. If the upstream pipeline is slower one day, you silently read incomplete data.

import pandas as pd, pytest
from transform import build_fact

def test_grain_is_unique():
    src = pd.DataFrame({"order_id":[1,1,2],"line_no":[1,2,1],"qty":[1,2,3],"price":[10,10,5]})
    out = build_fact(src)
    assert not out.duplicated(subset=["order_id","line_no"]).any()

def test_amount_calculation():
    src = pd.DataFrame({"order_id":[1],"line_no":[1],"qty":[3],"price":[1000]})
    assert build_fact(src)["net_amount"].iloc[0] == 3000

@pytest.mark.parametrize("bad", [None, -5])
def test_rejects_invalid_qty(bad):
    src = pd.DataFrame({"order_id":[1],"line_no":[1],"qty":[bad],"price":[10]})
    assert len(build_fact(src)) == 0

The key to testability is that transformation functions are pure: they take a DataFrame and return a DataFrame, without connecting to a database. Then tests run fast and without infrastructure.

from concurrent.futures import ThreadPoolExecutor, as_completed

def load_partition(day):
    df = extract(day)
    load(transform(df), partition=day)
    return day, len(df)

days = pd.date_range("2024-07-01", "2024-07-31").date
with ThreadPoolExecutor(max_workers=4) as ex:
    futures = {ex.submit(load_partition, d): d for d in days}
    for f in as_completed(futures):
        try:
            d, n = f.result()
            log.info("%s → %d rows", d, n)
        except Exception as e:
            log.error("%s failed: %s", futures[f], e)

Three conditions for safe parallelism: each unit of work writes to an independent partition, the source can handle the concurrent load, and one unit's failure doesn't stop the others. Without the first condition, two workers write to the same table and you get locks and deadlocks.

CREATE TABLE etl_sla (
  table_name text PRIMARY KEY,
  owner      text,
  sla_hours  int,
  schedule   text
);
INSERT INTO etl_sla VALUES
 ('fact_sales',   'data-team', 6,  'daily 2 AM'),
 ('dim_customer', 'data-team', 24, 'daily 1 AM');

-- Alert
SELECT s.table_name, s.owner, f.max_loaded_at,
       ROUND(EXTRACT(EPOCH FROM (now() - f.max_loaded_at))/3600, 1) AS hours_stale
FROM etl_sla s JOIN etl_freshness f USING (table_name)
WHERE now() - f.max_loaded_at > (s.sla_hours || ' hours')::interval;

The time commitment must be agreed with the stakeholder, not announced unilaterally by the data team. The right question is: “If this table is six hours behind, which decision is harmed?” The answer is the SLA value.