SQL coding interviews should reveal whether a candidate can turn a business requirement into a query with the right output grain. A query that runs can still double-count revenue, erase inactive customers, choose arbitrary tied rows, or move an event into the wrong reporting day. Strong candidates state the schema assumptions, write a solution, and test the edge cases that could change the answer.
This collection contains 120 hands-on problems and worked solutions across three levels. It progresses from filtering and joins to analytical windows, temporal logic, recursive relationships, and correctness-conscious optimization. Unlike a general SQL definitions guide or a PostgreSQL operations interview, it centers on writing and explaining queries. Ask candidates to predict results on a small fixture, then introduce a duplicate, null, tie, missing group, or changed time boundary.
How can recruiters tell whether a candidate can reason through SQL results rather than merely reproduce a plausible query?
Ask the candidate to explain the output grain, walk through an outer join on a small dataset, and repair a solution after a new tie or missing-value case appears. Those follow-ups reveal whether the candidate understands the query rather than only its syntax.
Sherlock AI helps recruiters assess signals of potential AI cheating, deepfakes, impersonation, and unauthorized assistance during interviews.
Use those signals alongside the candidate’s query walkthrough and technical follow-up, not as conclusive proof of misconduct.
SQL Coding Interview Questions
Choose problems from the complete bank below before consulting the solutions. Establish the dialect, table keys, missing-value policy, and required output before asking the candidate to code.
SQL Coding Interview Questions for Beginners
Start with row selection, null handling, relationship joins, aggregate summaries, set membership, and simple checks of output grain.
How do you return only paid orders worth at least 100, with the largest first?
How do you find orders in a UTC calendar month without losing the last day's timestamps?
How do you include customers whose country is unknown when excluding US customers?
How do you label orders as small, medium, or large using non-overlapping amount bands?
How do you display a fallback country label without changing the stored value?
How do you return the ten cheapest products with a stable result when prices tie?
How do you list the distinct known countries represented by customers?
How do you find product names beginning with the literal text Pro?
How do you calculate a line amount and identify expensive order lines?
How do you find employees with missing salary data without confusing missing with zero?
How do you attach a customer's name to every order without merging repeated names?
How do you list all customers together with any paid orders while retaining customers with none?
How do you find customers who have never placed an order?
How do you find products that appear on at least one paid order without returning duplicates?
How do you show every employee with their manager's name, including top-level employees?
How do you count all orders and paid orders in a single summary row?
How do you report paid revenue per customer, including zero for customers without paid orders?
How do you find customers with at least three paid orders?
How do you count employees and known salaries separately for each department?
How do you calculate average known salary by department without treating missing salaries as zero?
How do you find the highest and lowest product price in each category?
How do you find products priced above the catalog's average price?
How do you find employees earning more than their own department's known average salary?
How do you identify duplicated customer names without claiming they are duplicate customers?
How do you combine customers with paid orders or login events without duplicate customer IDs?
How do you combine order and event records into an activity list without discarding coincident activity?
How do you find customers who have both a paid order and a login event?
How do you find customers with a paid order but no refund event?
How do you return the earliest order timestamp for every customer, including customers without orders?
How do you report customers' paid and canceled order counts side by side?
How do you calculate the percentage of orders that are paid without dividing by zero?
How do you compute total units and weighted average selling price for each purchased product?
How do you find paid orders whose stored total differs from the sum of their lines?
How do you find employees who earn more than their immediate manager?
How do you generate every department-and-status combination for a reporting template?
How do you identify unordered pairs of customers in the same known country exactly once?
How do you find orders containing at least two distinct products rather than merely two lines?
How do you list products that have never appeared on any order line?
How do you find each customer's largest paid order amount while preserving customers without paid orders?
How do you verify that a customer-order join has not accidentally multiplied order rows?
SQL Coding Interview Questions for Intermediate
Use these exercises to test analytical windows, ordered selection, multi-stage aggregation, relational division, date scaffolds, gaps and islands, and practical reporting.
How do you return exactly two highest-paid employees per department?
How do you return every employee in the top two distinct salary bands per department?
How do you find the second distinct highest salary in each department?
How do you compute each paid order's share of its customer's paid revenue?
How do you calculate a deterministic running paid-revenue total for each customer?
How do you compare each paid order with the customer's previous paid order amount?
How do you identify the first paid order for every customer?
How do you attach each customer's final paid-order amount to all their paid orders?
How do you calculate a moving average over the current and previous two paid orders?
How do you assign employees to four approximately equal-sized salary groups?
How do you retain the newest event from each duplicate business-key group without deleting data?
How do you build a paid-order amount histogram that retains empty buckets?
How do you find customers who bought every product in a required product set?
How do you find customers whose purchased-product set is exactly the required set?
How do you calculate daily paid revenue in UTC rather than the session time zone?
How do you include zero-revenue days in a fixed seven-day report?
How do you calculate a seven-calendar-day moving revenue total including empty days?
How do you compare January paid revenue with December and avoid division by zero?
How do you find consecutive integer activity-day streaks after removing duplicate days?
How do you report missing integer day numbers between two endpoints?
How do you assign customers to first-purchase-month cohorts and count cohort sizes?
How do you calculate the average paid-order value per purchasing customer and then average those customer averages?
How do you list paid orders above their own customer's average without collapsing the order rows?
How do you report each employee's salary difference from the department average excluding that employee?
How do you count unique purchasing customers by product category without double-counting repeat orders?
How do you find the most frequently purchased product in each category, including ties?
How do you summarize order totals and paid amounts together without multiplying either measure?
How do you return nonempty departments where every employee has a known salary above a threshold?
How do you select products bought by both of two customers but not by a third?
How do you compare every pair of products co-purchased in the same paid order?
How do you calculate the 90th-percentile known salary in each department?
How do you calculate an exact-numeric median without a percentile aggregate?
How do you find each customer's first paid order that brings cumulative revenue to at least 100?
How do you find paid orders immediately preceded by two canceled orders for the same customer?
How do you measure elapsed hours between a customer's consecutive paid orders?
How do you find event rows where a customer's event type changed from the previous row?
How do you turn fixed monthly revenue columns into one row per month?
How do you return country paid-revenue subtotals and a grand total while distinguishing NULL countries?
How do you report the distribution of customers by their number of paid purchases, including nonbuyers?
How do you return only departments whose total known payroll exceeds the average department payroll?
SQL Coding Interview Questions for Experienced
These scenarios cover hierarchy traversal, temporal attribution, retention, sequence analysis, interval logic, reconciliation, pagination, and safe data changes.
How would you list every employee below a specified manager without looping on corrupt hierarchy data?
How would you calculate the salary budget for every manager's complete subtree?
How would you find the shortest hop count between nodes in a small directed graph?
How would you divide customer events into sessions using a thirty-minute inactivity threshold?
How would you find a customer's first purchase that follows a signup within twenty-four hours?
How would you count customers completing an ordered view-cart-purchase funnel?
How would you calculate day-seven retention without counting users whose observation window is incomplete?
How would you calculate monthly cohort retention with zero-filled month offsets?
How would you compute a seven-day rolling distinct active-customer count?
How would you measure the time to the next different event type while ignoring repeated identical events?
How would you match each order to the price that was effective when it was placed?
How would you detect overlapping validity intervals for the same entity?
How would you calculate peak concurrent bookings without counting adjacent bookings as overlapping?
How would you find gaps between a customer's paid orders that exceed ninety elapsed days?
How would you allocate an order-level discount proportionally across lines while preserving the exact cent total?
How would you find the payment that first brings an order's cumulative receipts up to its total?
How would you compare two snapshots as multisets rather than just sets?
How would you compare nullable attributes without missing changes?
How would you audit composite foreign keys when partially NULL keys must be rejected?
How would you find candidate-role matches when every required skill has a minimum proficiency?
How would you fetch the next page of orders without a large OFFSET?
How would you page customers by a nullable country without losing NULL-valued rows?
How would you split an OR filter into UNION ALL branches without duplicating rows that satisfy both conditions?
How would you express a New York business day without assuming it always lasts twenty-four hours?
How would you demonstrate why replacing NOT EXISTS with NOT IN can silently remove every unmatched row?
How would you remove a join that exists only to require at least one matching child row?
How would you inspect the execution of an aggregate query without confusing estimated rows with actual rows?
How would you count customers whose total paid revenue is below 100, including nonbuyers?
How would you find a minimum set of high-value orders reaching eighty percent of revenue?
How would you attribute revenue across overlapping tags without inflating the grand total?
How would you delete duplicate staging rows while retaining the latest complete record?
How would you upsert a snapshot without allowing an older update to overwrite a newer one?
How would you update order summaries safely when the source has multiple detail rows?
How would you decrement inventory without allowing a single-row stock value to become negative?
How would workers claim different pending jobs without blocking behind already claimed rows?
How would you pair duplicate occurrences from two datasets one-to-one instead of creating a many-to-many join?
How would you allocate issued inventory to receipts using FIFO without procedural loops?
How would you find the first inventory movement that makes a product's running balance negative?
How would you find customers who buy from a category only after their first purchase from another category?
How would you reconcile a hierarchical rollup against its leaf-level source without double-counting stored subtotal rows?
SQL Coding Interview Questions and Answers
Every answer supplies a worked query and explains its output contract. Solutions target PostgreSQL 16 or later, with PostgreSQL-specific constructs called out where relevant; SQL dialects are not interchangeable. A portable subset was executed against isolated SQLite fixtures, not PostgreSQL. PostgreSQL-specific timestamp, array, recursion-path, planner, and mutation examples were reviewed against primary documentation but were not executed on a PostgreSQL server. Those local tests establish only the tested fixture results, not production correctness, cross-engine equivalence, or performance.
Unless a question declares its own data, use the following shared schema. IDs are primary keys except order_items, whose key is (order_id, line_no). Customer and product names are non-null; country, category, salary, department_id, and manager_id may be null. Fact-table customer, order, and product references are non-null. Valid non-null references point to the corresponding parent. Amounts and prices are exact numeric values in one currency; total and price are nonnegative, quantity is positive, and unit_price and payment amount are non-null. All event and order timestamps are non-null timestamptz values. Default revenue means the stored total of orders with status = 'paid', not payment receipts or line totals. Calendar exercises state their time zone and use half-open boundaries. Unless ORDER BY is present, output row order is unspecified. Auxiliary VALUES fixtures and tables are local to their own exercise, not additions to the shared business model.
CREATE TABLE customers(customer_id integer PRIMARY KEY, name text NOT NULL, country text);
CREATE TABLE products(product_id integer PRIMARY KEY, name text NOT NULL, category text, price numeric(12,2) NOT NULL CHECK(price>=0));
CREATE TABLE orders(order_id integer PRIMARY KEY, customer_id integer NOT NULL REFERENCES customers, ordered_at timestamptz NOT NULL, status text NOT NULL, total numeric(12,2) NOT NULL CHECK(total>=0));
CREATE TABLE order_items(order_id integer NOT NULL REFERENCES orders, line_no integer, product_id integer NOT NULL REFERENCES products, quantity integer NOT NULL CHECK(quantity>0), unit_price numeric(12,2) NOT NULL, PRIMARY KEY(order_id,line_no));
CREATE TABLE departments(department_id integer PRIMARY KEY, name text NOT NULL);
CREATE TABLE employees(employee_id integer PRIMARY KEY, name text NOT NULL, department_id integer REFERENCES departments, manager_id integer REFERENCES employees, salary numeric(12,2));
CREATE TABLE events(event_id integer PRIMARY KEY, customer_id integer NOT NULL REFERENCES customers, occurred_at timestamptz NOT NULL, event_type text NOT NULL);
CREATE TABLE payments(payment_id integer PRIMARY KEY, order_id integer NOT NULL REFERENCES orders, amount numeric(12,2) NOT NULL, paid_at timestamptz NOT NULL)
CREATE TABLE customers(customer_id integer PRIMARY KEY, name text NOT NULL, country text);
CREATE TABLE products(product_id integer PRIMARY KEY, name text NOT NULL, category text, price numeric(12,2) NOT NULL CHECK(price>=0));
CREATE TABLE orders(order_id integer PRIMARY KEY, customer_id integer NOT NULL REFERENCES customers, ordered_at timestamptz NOT NULL, status text NOT NULL, total numeric(12,2) NOT NULL CHECK(total>=0));
CREATE TABLE order_items(order_id integer NOT NULL REFERENCES orders, line_no integer, product_id integer NOT NULL REFERENCES products, quantity integer NOT NULL CHECK(quantity>0), unit_price numeric(12,2) NOT NULL, PRIMARY KEY(order_id,line_no));
CREATE TABLE departments(department_id integer PRIMARY KEY, name text NOT NULL);
CREATE TABLE employees(employee_id integer PRIMARY KEY, name text NOT NULL, department_id integer REFERENCES departments, manager_id integer REFERENCES employees, salary numeric(12,2));
CREATE TABLE events(event_id integer PRIMARY KEY, customer_id integer NOT NULL REFERENCES customers, occurred_at timestamptz NOT NULL, event_type text NOT NULL);
CREATE TABLE payments(payment_id integer PRIMARY KEY, order_id integer NOT NULL REFERENCES orders, amount numeric(12,2) NOT NULL, paid_at timestamptz NOT NULL)
CREATE TABLE customers(customer_id integer PRIMARY KEY, name text NOT NULL, country text);
CREATE TABLE products(product_id integer PRIMARY KEY, name text NOT NULL, category text, price numeric(12,2) NOT NULL CHECK(price>=0));
CREATE TABLE orders(order_id integer PRIMARY KEY, customer_id integer NOT NULL REFERENCES customers, ordered_at timestamptz NOT NULL, status text NOT NULL, total numeric(12,2) NOT NULL CHECK(total>=0));
CREATE TABLE order_items(order_id integer NOT NULL REFERENCES orders, line_no integer, product_id integer NOT NULL REFERENCES products, quantity integer NOT NULL CHECK(quantity>0), unit_price numeric(12,2) NOT NULL, PRIMARY KEY(order_id,line_no));
CREATE TABLE departments(department_id integer PRIMARY KEY, name text NOT NULL);
CREATE TABLE employees(employee_id integer PRIMARY KEY, name text NOT NULL, department_id integer REFERENCES departments, manager_id integer REFERENCES employees, salary numeric(12,2));
CREATE TABLE events(event_id integer PRIMARY KEY, customer_id integer NOT NULL REFERENCES customers, occurred_at timestamptz NOT NULL, event_type text NOT NULL);
CREATE TABLE payments(payment_id integer PRIMARY KEY, order_id integer NOT NULL REFERENCES orders, amount numeric(12,2) NOT NULL, paid_at timestamptz NOT NULL)
SQL Coding Interview Questions and Answers for Beginners
These solutions make basic choices explicit: preserve or exclude unmatched rows, count records or values, and distinguish zero from missing data. The shared schema applies unless an answer says otherwise.
1. How do you return only paid orders worth at least 100, with the largest first?
Filter the order rows before sorting, and use the primary key as a deterministic tie-breaker. The output contains one row per qualifying order, not one per customer. total is the stored order amount; this exercise does not reconcile payments or item totals.
SELECT order_id, customer_id, total
FROM orders
WHERE status = 'paid' AND total >= 100
ORDER BY total DESC, order_id ASC
SELECT order_id, customer_id, total
FROM orders
WHERE status = 'paid' AND total >= 100
ORDER BY total DESC, order_id ASC
SELECT order_id, customer_id, total
FROM orders
WHERE status = 'paid' AND total >= 100
ORDER BY total DESC, order_id ASC
An order worth exactly 100 qualifies. Equal totals remain separate orders and sort by ID. Neither DISTINCT nor aggregation belongs here: they could obscure the requested grain. The schema makes total and status non-null; if a different schema permits null totals, the comparison would exclude them rather than treating them as zero. A useful interview follow-up is changing the requirement to the highest-value order per customer, which requires an additional selection rule.
2. How do you find orders in a UTC calendar month without losing the last day's timestamps?
Use an inclusive lower bound and exclusive upper bound. This PostgreSQL query defines January 2026 in UTC explicitly and returns each matching order once, regardless of its status. Typed TIMESTAMPTZ literals are PostgreSQL syntax; adapt typed parameters when using another engine.
SELECT order_id, ordered_at
FROM orders
WHERE ordered_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
AND ordered_at < TIMESTAMPTZ '2026-02-01 00:00:00+00'
ORDER BY ordered_at,
SELECT order_id, ordered_at
FROM orders
WHERE ordered_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
AND ordered_at < TIMESTAMPTZ '2026-02-01 00:00:00+00'
ORDER BY ordered_at,
SELECT order_id, ordered_at
FROM orders
WHERE ordered_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
AND ordered_at < TIMESTAMPTZ '2026-02-01 00:00:00+00'
ORDER BY ordered_at,
The upper bound excludes midnight on February 1 while retaining all supported fractional-second precision on January 31. Do not manufacture an end-of-day value ending in 23:59:59; that can omit later fractions. Comparing the original timestamp column also avoids wrapping it in a month-extraction expression. A business month in another time zone needs its own converted boundaries, not a silently reused UTC month.
3. How do you include customers whose country is unknown when excluding US customers?
Make the null case explicit because an unknown country is not automatically unequal to US. The requested output is every customer except those whose stored country is exactly US; it includes null country values.
SELECT customer_id, name, country
FROM customers
WHERE country <> 'US' OR country IS NULL
ORDER BY
SELECT customer_id, name, country
FROM customers
WHERE country <> 'US' OR country IS NULL
ORDER BY
SELECT customer_id, name, country
FROM customers
WHERE country <> 'US' OR country IS NULL
ORDER BY
country <> 'US' alone drops nulls because its result is unknown, and a WHERE clause retains only true conditions. This version does not normalize country spelling, capitalization, or whitespace: those are separate data-contract decisions. Duplicate names are allowed and do not merge customers because identity comes from customer_id. If unknown countries should instead be excluded for compliance reporting, omit the second predicate deliberately rather than allowing three-valued logic to make the business decision accidentally.
4. How do you label orders as small, medium, or large using non-overlapping amount bands?
Use a searched CASE in increasing threshold order. Define small as below 100, medium as 100 through values below 500, and large as at least 500. The schema guarantees a non-null, nonnegative total, so every order receives exactly one label.
SELECT order_id, total,
CASE WHEN total < 100 THEN 'small'
WHEN total < 500 THEN 'medium'
ELSE 'large' END AS amount_band
FROM orders
ORDER BY
SELECT order_id, total,
CASE WHEN total < 100 THEN 'small'
WHEN total < 500 THEN 'medium'
ELSE 'large' END AS amount_band
FROM orders
ORDER BY
SELECT order_id, total,
CASE WHEN total < 100 THEN 'small'
WHEN total < 500 THEN 'medium'
ELSE 'large' END AS amount_band
FROM orders
ORDER BY
The first matching condition wins, so the second branch does not need to repeat total >= 100. Values exactly at a boundary belong to the next band. This computes a display classification without changing stored data. If null totals become legal, add an initial WHEN total IS NULL branch; otherwise an unknown amount would fall into ELSE and be incorrectly labeled large.
5. How do you display a fallback country label without changing the stored value?
Apply COALESCE only in the projection. It substitutes a display label for null country values while preserving one row per customer and leaving the underlying table unchanged.
SELECT customer_id, name,
COALESCE(country, 'Unknown') AS country_label
FROM customers
ORDER BY
SELECT customer_id, name,
COALESCE(country, 'Unknown') AS country_label
FROM customers
ORDER BY
SELECT customer_id, name,
COALESCE(country, 'Unknown') AS country_label
FROM customers
ORDER BY
An empty string is not null and therefore remains an empty string. If the input contract treats blank strings as missing, use COALESCE(NULLIF(country, ''), 'Unknown') instead, with an explicit decision about whitespace. A real stored country value equal to Unknown becomes visually indistinguishable from the fallback, so this label should not serve as a durable grouping key or identity field. Keep the raw country as well when downstream consumers must distinguish missing data from a legitimate stored value.
6. How do you return the ten cheapest products with a stable result when prices tie?
Sort first by price, then by the unique product ID, and cap the result at ten rows. This task requests at most ten products, not every product tied at the tenth price. LIMIT is supported by PostgreSQL but is not the universal SQL row-limiting syntax.
SELECT product_id, name, price
FROM products
ORDER BY price ASC, product_id ASC
LIMIT 10
SELECT product_id, name, price
FROM products
ORDER BY price ASC, product_id ASC
LIMIT 10
SELECT product_id, name, price
FROM products
ORDER BY price ASC, product_id ASC
LIMIT 10
The secondary ordering makes selection reproducible for unchanged data. A table with fewer than ten products returns all of them, and an empty table returns no rows. The price column is non-null under the shared contract; otherwise a null-placement policy would also be necessary. Sorting only by price leaves the membership of the last tied position unstable. If the requirement changes to include all price ties, use a rank-based selection rather than claiming this query meets that different contract.
7. How do you list the distinct known countries represented by customers?
Remove missing countries before applying DISTINCT. The result has one row per distinct non-null stored country value, rather than one row per customer.
SELECT DISTINCT country
FROM customers
WHERE country IS NOT NULL
ORDER BY
SELECT DISTINCT country
FROM customers
WHERE country IS NOT NULL
ORDER BY
SELECT DISTINCT country
FROM customers
WHERE country IS NOT NULL
ORDER BY
Multiple customers from the same country contribute only one output row. Without the filter, DISTINCT would include one null group, which is not a known country. Empty strings, alternate spellings, and differing case are not normalized by this query; whether those values compare as equal depends on the database's collation and data choices. Production country reporting is usually clearer with validated country codes. Do not include customer_id in the select list if the goal is unique countries, because uniqueness would then apply to the entire selected tuple.
8. How do you find product names beginning with the literal text Pro?
Use a prefix pattern with a trailing wildcard. This PostgreSQL example assumes a deterministic, case-sensitive collation and asks for the literal prefix Pro, not a case-insensitive search and not a substring appearing anywhere.
SELECT product_id, name
FROM products
WHERE name LIKE 'Pro%'
ORDER BY
SELECT product_id, name
FROM products
WHERE name LIKE 'Pro%'
ORDER BY
SELECT product_id, name
FROM products
WHERE name LIKE 'Pro%'
ORDER BY
The percent sign matches zero or more following characters, so the exact name Pro also qualifies. An underscore in a pattern represents one character rather than a literal underscore; user-supplied prefixes therefore need a defined escaping strategy as well as bound parameters. PostgreSQL's ILIKE can express case-insensitive matching, but that would change the requirement and is nonportable. Whether an index supports the prefix search depends on collation, operator classes, and the chosen plan; a visually simple predicate is not itself performance evidence.
9. How do you calculate a line amount and identify expensive order lines?
Compute quantity times the historical unit price stored on the order line. Join-free calculation preserves one row per (order_id, line_no), including repeated purchases of the same product on different lines.
SELECT order_id, line_no, product_id,
quantity * unit_price AS line_amount
FROM order_items
WHERE quantity * unit_price >= 100
ORDER BY order_id,
SELECT order_id, line_no, product_id,
quantity * unit_price AS line_amount
FROM order_items
WHERE quantity * unit_price >= 100
ORDER BY order_id,
SELECT order_id, line_no, product_id,
quantity * unit_price AS line_amount
FROM order_items
WHERE quantity * unit_price >= 100
ORDER BY order_id,
Do not substitute the current product price: the product catalog may have changed since the purchase. Repeating the expression in WHERE is clear here because a select-list alias is not available at that processing stage. This computes merchandise line value only; taxes, shipping, and discounts may explain a difference from the stored order total. PostgreSQL's numeric arithmetic avoids binary floating-point rounding in this schema. Any real currency-mixing scenario also requires a currency dimension rather than summing incomparable amounts.
10. How do you find employees with missing salary data without confusing missing with zero?
Test for null directly. This output contains one row per employee whose salary has not been recorded, and excludes employees whose known salary is zero.
SELECT employee_id, name, department_id
FROM employees
WHERE salary IS NULL
ORDER BY
SELECT employee_id, name, department_id
FROM employees
WHERE salary IS NULL
ORDER BY
SELECT employee_id, name, department_id
FROM employees
WHERE salary IS NULL
ORDER BY
salary = NULL does not test for missingness; it produces an unknown comparison result and cannot select these rows. Likewise, replacing salary with zero before filtering would collapse two distinct business states. A null may mean unknown, not applicable, or withheld, and a richer schema might represent those reasons separately. This query reports missing values without inferring their reason. When explaining the answer, show both a null-salary row and a zero-salary row in the test fixture so that the intended distinction is observable.
11. How do you attach a customer's name to every order without merging repeated names?
Join on the customer key and retain the order key. The result has one row per order whose customer exists; the declared foreign key supplies that match for non-null customer IDs.
SELECT o.order_id, c.customer_id, c.name, o.total
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY
SELECT o.order_id, c.customer_id, c.name, o.total
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY
SELECT o.order_id, c.customer_id, c.name, o.total
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY
Two customers can share a name and must remain distinct. Joining on name would create incorrect matches and potentially multiply rows. This join does not sum amounts or apply paid-status filtering because neither is requested. If a staging dataset lacks referential integrity and orphaned orders must remain visible, use a left join and inspect the missing customer fields instead. The choice of join type expresses an output contract, not merely a preferred style of SQL syntax.
12. How do you list all customers together with any paid orders while retaining customers with none?
Place the paid-status condition in the outer join's ON clause. The output grain is one row per paid order plus one null-extended row for each customer without a paid order.
SELECT c.customer_id, c.name, o.order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.status = 'paid'
ORDER BY c.customer_id,
SELECT c.customer_id, c.name, o.order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.status = 'paid'
ORDER BY c.customer_id,
SELECT c.customer_id, c.name, o.order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.status = 'paid'
ORDER BY c.customer_id,
A customer with only canceled orders receives a null-extended row just like a customer with no orders. Putting o.status = 'paid' in WHERE would reject those rows and effectively require a matching order. Do not claim the result is one row per customer: customers with several paid orders appear several times. If the requested output instead needs a customer summary, aggregate after joining and count the non-null order key rather than the outer-join placeholder.
13. How do you find customers who have never placed an order?
Use a correlated NOT EXISTS test. It selects each customer for whom no matching order row exists, irrespective of order status or amount.
SELECT c.customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.customer_id
)
ORDER BY
SELECT c.customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.customer_id
)
ORDER BY
SELECT c.customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.customer_id
)
ORDER BY
Several orders do not multiply a customer's row because the subquery is only an existence test. A null customer reference on an unrelated order does not poison the result, unlike a careless NOT IN expression over a nullable column. A canceled order still counts as an order under this wording. To find customers with no paid orders instead, add the paid predicate inside the subquery. Keeping the qualifying condition inside the anti-match makes that business difference easy to inspect and test.
14. How do you find products that appear on at least one paid order without returning duplicates?
Use EXISTS over the qualifying order lines. The outer product table determines the output grain, so a product appears once even when it was purchased on many lines or orders.
SELECT p.product_id, p.name
FROM products AS p
WHERE EXISTS (
SELECT 1
FROM order_items AS i
JOIN orders AS o ON o.order_id = i.order_id
WHERE i.product_id = p.product_id AND o.status = 'paid'
)
ORDER BY
SELECT p.product_id, p.name
FROM products AS p
WHERE EXISTS (
SELECT 1
FROM order_items AS i
JOIN orders AS o ON o.order_id = i.order_id
WHERE i.product_id = p.product_id AND o.status = 'paid'
)
ORDER BY
SELECT p.product_id, p.name
FROM products AS p
WHERE EXISTS (
SELECT 1
FROM order_items AS i
JOIN orders AS o ON o.order_id = i.order_id
WHERE i.product_id = p.product_id AND o.status = 'paid'
)
ORDER BY
This answers whether a purchase exists, not how many purchases occurred. An ordinary join would emit one row for each matching line and require deduplication if the same grain were desired. A paid order with no lines contributes no products. The catalog price and the order's total do not affect membership; only a matching line on a paid order does. Add quantity or monetary thresholds explicitly if the exercise requires them.
15. How do you show every employee with their manager's name, including top-level employees?
Self-join the employee table using separate aliases and preserve the employee side. Each employee has at most one referenced manager, so the result remains one row per employee.
SELECT e.employee_id, e.name AS employee_name,
m.employee_id AS manager_id, m.name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m ON m.employee_id = e.manager_id
ORDER BY
SELECT e.employee_id, e.name AS employee_name,
m.employee_id AS manager_id, m.name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m ON m.employee_id = e.manager_id
ORDER BY
SELECT e.employee_id, e.name AS employee_name,
m.employee_id AS manager_id, m.name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m ON m.employee_id = e.manager_id
ORDER BY
A top-level employee with a null manager ID receives null manager fields. Two managers with the same name remain distinct because the relationship uses IDs. The query reports only the immediate manager; it does not traverse the entire hierarchy or detect cycles. If foreign keys are absent in an imported table, a dangling manager reference also produces null manager fields, so distinguish that data-quality error from a legitimate root by additionally selecting the original e.manager_id.
16. How do you count all orders and paid orders in a single summary row?
Use an unconditional count alongside a conditional aggregate. The result is exactly one row even when the order table is empty.
SELECT COUNT(*) AS all_orders,
COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders
FROM
SELECT COUNT(*) AS all_orders,
COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders
FROM
SELECT COUNT(*) AS all_orders,
COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders
FROM
The CASE returns null for non-paid orders, and COUNT(expression) ignores those null values. This is a count of order rows, not distinct customers and not payment transactions. Both counts return zero on empty input, avoiding the null result that a bare conditional SUM could produce. Multiple payment records cannot inflate the counts because payments are not joined. If an order can have several status history records in a different schema, first derive one current order record; this query assumes the shared table already holds one row per order.
17. How do you report paid revenue per customer, including zero for customers without paid orders?
Left-join qualifying orders, aggregate by customer identity, and replace an empty sum with zero. The paid condition belongs in ON so customers without qualifying orders survive.
SELECT c.customer_id, c.name,
COALESCE(SUM(o.total), 0) AS paid_revenue
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id, c.name
ORDER BY
SELECT c.customer_id, c.name,
COALESCE(SUM(o.total), 0) AS paid_revenue
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id, c.name
ORDER BY
SELECT c.customer_id, c.name,
COALESCE(SUM(o.total), 0) AS paid_revenue
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id, c.name
ORDER BY
The output is one row per customer. Two paid orders with the same total must both contribute; SUM(DISTINCT o.total) would incorrectly discard one. The query intentionally uses stored paid order totals rather than payments received, so partial payments do not redefine revenue here. It also does not join line items, which would duplicate the order amount once per line. On an empty customer table there are no groups and therefore no output rows.
18. How do you find customers with at least three paid orders?
Filter orders before grouping, then apply the group-level threshold with HAVING. This yields one row per qualifying customer ID and counts each paid order once.
SELECT customer_id, COUNT(*) AS paid_order_count
FROM orders
WHERE status = 'paid' AND customer_id IS NOT NULL
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY
SELECT customer_id, COUNT(*) AS paid_order_count
FROM orders
WHERE status = 'paid' AND customer_id IS NOT NULL
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY
SELECT customer_id, COUNT(*) AS paid_order_count
FROM orders
WHERE status = 'paid' AND customer_id IS NOT NULL
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY
The explicit non-null condition avoids treating unassigned orders as a customer group if the nullable foreign-key column is allowed to contain null. A threshold of three includes exactly three. WHERE COUNT(*) >= 3 is not valid because row filtering happens before the aggregate is available. If customer names are needed, join this grouped result to customers afterward or group by the required customer columns. A customer with many canceled orders but fewer than three paid orders must not qualify.
19. How do you count employees and known salaries separately for each department?
Start with departments to include empty ones, then count the employee key and salary expression separately. The output has one row per department, including zero-count departments.
SELECT d.department_id, d.name,
COUNT(e.employee_id) AS employee_count,
COUNT(e.salary) AS known_salary_count
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.department_id
GROUP BY d.department_id, d.name
ORDER BY
SELECT d.department_id, d.name,
COUNT(e.employee_id) AS employee_count,
COUNT(e.salary) AS known_salary_count
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.department_id
GROUP BY d.department_id, d.name
ORDER BY
SELECT d.department_id, d.name,
COUNT(e.employee_id) AS employee_count,
COUNT(e.salary) AS known_salary_count
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.department_id
GROUP BY d.department_id, d.name
ORDER BY
COUNT(*) would count the placeholder row for an empty department and incorrectly report one employee. A null salary reduces only known_salary_count, while a known zero salary contributes to both counts. Employees with a null department are not represented because the requested dimension is the department table; add a separate unassigned bucket if needed. This illustrates why the counted expression should follow the measurement's meaning rather than using the same count syntax automatically everywhere.
20. How do you calculate average known salary by department without treating missing salaries as zero?
Average the salary column directly. The output contains one row for each department ID represented among employees, including a null-ID group for unassigned employees.
SELECT department_id, AVG(salary) AS average_known_salary,
COUNT(salary) AS salary_observations
FROM employees
GROUP BY department_id
ORDER BY department_id NULLS LAST
SELECT department_id, AVG(salary) AS average_known_salary,
COUNT(salary) AS salary_observations
FROM employees
GROUP BY department_id
ORDER BY department_id NULLS LAST
SELECT department_id, AVG(salary) AS average_known_salary,
COUNT(salary) AS salary_observations
FROM employees
GROUP BY department_id
ORDER BY department_id NULLS LAST
AVG ignores null salaries, so the denominator is the count of recorded salaries rather than all employees. A department with employees but no recorded salaries produces a null average and zero observations; this is not equivalent to a zero average. A department with no employees does not appear because the query starts from employees. NULLS LAST makes placement of the unassigned group explicit. Replacing missing salaries with zero would answer a different question and usually bias compensation reporting downward.
21. How do you find the highest and lowest product price in each category?
Group catalog products by category and compute the two extrema. The output has one row per represented category, with all null categories forming a single uncategorized group.
SELECT category, MIN(price) AS lowest_price,
MAX(price) AS highest_price, COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY category NULLS LAST
SELECT category, MIN(price) AS lowest_price,
MAX(price) AS highest_price, COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY category NULLS LAST
SELECT category, MIN(price) AS lowest_price,
MAX(price) AS highest_price, COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY category NULLS LAST
This reports price values rather than the products that own them; adding a product name without a selection rule would be ambiguous. Several products may share either extreme. An empty catalog produces no category rows, not a row of zero prices. Catalog prices are non-null by contract, so every product contributes to both extrema. If a separate category table defines categories with no products, start from that table with an outer join to include them and keep their missing extrema distinguishable from a real zero price.
22. How do you find products priced above the catalog's average price?
Compare each product price with a scalar aggregate over the same catalog. The aggregate returns one value, and the outer query preserves product-level rows.
SELECT product_id, name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC,
SELECT product_id, name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC,
SELECT product_id, name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC,
The comparison is strictly greater, so a product exactly at the mean does not qualify. Equal-priced qualifying products remain distinct. An empty catalog yields no outer rows; with one product, that product equals its own average and is excluded. This is the unweighted average of catalog prices, not an average selling price weighted by units sold. Changing the requirement to a category-relative average needs correlation or a grouped join, and should not be implemented by accidentally keeping the global average in place.
23. How do you find employees earning more than their own department's known average salary?
Use a correlated salary aggregate and explicitly restrict the exercise to assigned employees. The output is one row per employee with a known salary strictly above the mean of known salaries in that employee's department.
SELECT e.employee_id, e.name, e.salary
FROM employees AS e
WHERE e.department_id IS NOT NULL
AND e.salary > (
SELECT AVG(peer.salary)
FROM employees AS peer
WHERE peer.department_id = e.department_id
)
ORDER BY
SELECT e.employee_id, e.name, e.salary
FROM employees AS e
WHERE e.department_id IS NOT NULL
AND e.salary > (
SELECT AVG(peer.salary)
FROM employees AS peer
WHERE peer.department_id = e.department_id
)
ORDER BY
SELECT e.employee_id, e.name, e.salary
FROM employees AS e
WHERE e.department_id IS NOT NULL
AND e.salary > (
SELECT AVG(peer.salary)
FROM employees AS peer
WHERE peer.department_id = e.department_id
)
ORDER BY
Missing salaries do not contribute to the mean and cannot qualify in the outer comparison. The employee's own salary is included in the departmental average; excluding it would define a peer-only average and require another predicate. A department with a single known salary produces no qualifying employee. Unassigned employees are deliberately excluded rather than silently pooling them into a fictional department. Large-data performance should be assessed against a grouped alternative with the same semantics.
24. How do you identify duplicated customer names without claiming they are duplicate customers?
Group by the name value and retain groups containing more than one customer row. The result reports repeated labels and their frequency, not records that are safe to merge or delete.
SELECT name, COUNT(*) AS customer_count
FROM customers
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY customer_count DESC,
SELECT name, COUNT(*) AS customer_count
FROM customers
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY customer_count DESC,
SELECT name, COUNT(*) AS customer_count
FROM customers
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY customer_count DESC,
Two different people can legitimately share a name, so the primary key remains the identity. Comparison follows the column's collation, and case or spacing normalization would change what counts as a duplicate label. If the schema permits null names, those nulls form one group; decide separately whether missing names should be included. This query is useful as a data-profiling signal, but deduplication needs a business key and a retention policy. It should not become a destructive cleanup statement merely because several values match.
25. How do you combine customers with paid orders or login events without duplicate customer IDs?
Use UNION to form a set of IDs from the two qualifying sources. The output contains each non-null customer ID at most once, even if that customer has many orders, many events, or qualifies in both branches.
SELECT customer_id FROM orders
WHERE status = 'paid' AND customer_id IS NOT NULL
UNION
SELECT customer_id FROM events
WHERE event_type = 'login' AND customer_id IS NOT NULL
ORDER BY
SELECT customer_id FROM orders
WHERE status = 'paid' AND customer_id IS NOT NULL
UNION
SELECT customer_id FROM events
WHERE event_type = 'login' AND customer_id IS NOT NULL
ORDER BY
SELECT customer_id FROM orders
WHERE status = 'paid' AND customer_id IS NOT NULL
UNION
SELECT customer_id FROM events
WHERE event_type = 'login' AND customer_id IS NOT NULL
ORDER BY
Both branches project the same compatible key column. UNION ALL would preserve all contributing rows and therefore answer a different question. Adding source labels inside the union would also change deduplication, because uniqueness would then apply to the customer-and-source pair. The query imposes no time window; if activity must be recent, apply matching temporal boundaries independently in each branch using the correct timestamp column.
26. How do you combine order and event records into an activity list without discarding coincident activity?
Use UNION ALL with a source discriminator and source-local ID. Preserve every record because distinct orders and events can share a timestamp and customer.
SELECT 'order' AS activity_type, order_id AS activity_id,
customer_id, ordered_at AS activity_at
FROM orders
UNION ALL
SELECT 'event', event_id, customer_id, occurred_at
FROM events
ORDER BY activity_at, activity_type,
SELECT 'order' AS activity_type, order_id AS activity_id,
customer_id, ordered_at AS activity_at
FROM orders
UNION ALL
SELECT 'event', event_id, customer_id, occurred_at
FROM events
ORDER BY activity_at, activity_type,
SELECT 'order' AS activity_type, order_id AS activity_id,
customer_id, ordered_at AS activity_at
FROM orders
UNION ALL
SELECT 'event', event_id, customer_id, occurred_at
FROM events
ORDER BY activity_at, activity_type,
The unique identity of an output activity is the pair (activity_type, activity_id), not the numeric ID alone. Timestamp ordering is deterministic because the source and key break ties. Both timestamp columns share the same time-zone-aware type under the contract. This query intentionally includes all order statuses and event types. UNION would perform unnecessary duplicate elimination and can hide legitimate records in projections that omit source identity. A consumer requiring only login activity should filter that branch explicitly.
27. How do you find customers who have both a paid order and a login event?
Use two existence predicates, one for each required activity. This preserves customer grain and avoids a many-to-many join between a customer's orders and events.
SELECT c.customer_id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.customer_id AND o.status = 'paid'
)
AND EXISTS (
SELECT 1 FROM events AS e
WHERE e.customer_id = c.customer_id AND e.event_type = 'login'
)
ORDER BY
SELECT c.customer_id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.customer_id AND o.status = 'paid'
)
AND EXISTS (
SELECT 1 FROM events AS e
WHERE e.customer_id = c.customer_id AND e.event_type = 'login'
)
ORDER BY
SELECT c.customer_id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.customer_id AND o.status = 'paid'
)
AND EXISTS (
SELECT 1 FROM events AS e
WHERE e.customer_id = c.customer_id AND e.event_type = 'login'
)
ORDER BY
A customer with ten orders and twenty logins still produces one row, not two hundred joined combinations. The two events need not occur in any particular sequence or time window; those requirements are absent from this question. A login after an order therefore qualifies just as a login before it does. If the interview adds temporal attribution, the relationship between individual timestamps must become part of the predicate instead of being inferred from mere membership.
28. How do you find customers with a paid order but no refund event?
Combine a positive existence test with a negative one. The exercise defines a refund event at the customer level, so any recorded refund event excludes that customer regardless of which order it concerns.
SELECT c.customer_id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.customer_id AND o.status = 'paid'
)
AND NOT EXISTS (
SELECT 1 FROM events AS e
WHERE e.customer_id = c.customer_id AND e.event_type = 'refund'
)
ORDER BY
SELECT c.customer_id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.customer_id AND o.status = 'paid'
)
AND NOT EXISTS (
SELECT 1 FROM events AS e
WHERE e.customer_id = c.customer_id AND e.event_type = 'refund'
)
ORDER BY
SELECT c.customer_id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.customer_id AND o.status = 'paid'
)
AND NOT EXISTS (
SELECT 1 FROM events AS e
WHERE e.customer_id = c.customer_id AND e.event_type = 'refund'
)
ORDER BY
The output is one row per qualifying customer, unaffected by duplicate qualifying activities. The event schema has no order ID, so this query cannot honestly establish that a particular paid order was never refunded. It also imposes no event-time cutoff. For order-level net revenue, use a model with explicit refund-to-order linkage and amounts rather than stretching a customer-level event marker into financial evidence it does not contain.
29. How do you return the earliest order timestamp for every customer, including customers without orders?
Use an outer join and take the minimum timestamp per customer. The output contains one row per customer and a null timestamp when no order exists.
SELECT c.customer_id, MIN(o.ordered_at) AS first_order_at
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY
SELECT c.customer_id, MIN(o.ordered_at) AS first_order_at
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY
SELECT c.customer_id, MIN(o.ordered_at) AS first_order_at
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY
All order statuses count because the question asks for the first order, not the first paid order. Multiple orders at the same earliest timestamp still produce a single timestamp value. The query does not identify a unique first order ID; adding an arbitrary minimum ID could associate an ID with a different timestamp. Selecting an entire first-order row requires a consistent ordering rule. Missing first-order dates should remain null rather than being replaced with a fictional calendar date that could contaminate later age or retention calculations.
30. How do you report customers' paid and canceled order counts side by side?
Use conditional counts over a customer-preserving outer join. Each output row summarizes one customer, including customers with no orders.
SELECT c.customer_id,
COUNT(CASE WHEN o.status = 'paid' THEN 1 END) AS paid_count,
COUNT(CASE WHEN o.status = 'canceled' THEN 1 END) AS canceled_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY
SELECT c.customer_id,
COUNT(CASE WHEN o.status = 'paid' THEN 1 END) AS paid_count,
COUNT(CASE WHEN o.status = 'canceled' THEN 1 END) AS canceled_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY
SELECT c.customer_id,
COUNT(CASE WHEN o.status = 'paid' THEN 1 END) AS paid_count,
COUNT(CASE WHEN o.status = 'canceled' THEN 1 END) AS canceled_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY
The null-extended row contributes to neither conditional count, so an inactive customer receives two zeroes. Orders in any other status are intentionally excluded from both measures. Status spelling is part of the contract: this example uses canceled, not an alternate spelling. These measures need not sum to total orders if other statuses exist. Joining payments or items before counting would introduce fan-out, so add separately aggregated measures rather than extending the join indiscriminately.
31. How do you calculate the percentage of orders that are paid without dividing by zero?
Use a conditional numerator, a count denominator, and an explicit empty-input policy. This query returns one row; an empty table produces a null percentage because the proportion is undefined, not zero.
SELECT 100.0 * COUNT(CASE WHEN status = 'paid' THEN 1 END)
/ NULLIF(COUNT(*), 0) AS paid_percentage
FROM
SELECT 100.0 * COUNT(CASE WHEN status = 'paid' THEN 1 END)
/ NULLIF(COUNT(*), 0) AS paid_percentage
FROM
SELECT 100.0 * COUNT(CASE WHEN status = 'paid' THEN 1 END)
/ NULLIF(COUNT(*), 0) AS paid_percentage
FROM
The decimal constant prevents integer division from truncating the ratio. NULLIF converts a zero denominator to null, making the division safe. A table containing orders but no paid ones returns zero, which is meaningfully different from no orders at all. This is the percentage of order records, not the percentage of customers who purchased and not the share of revenue. Formatting or rounding can be applied at presentation time; doing it too early can hide small but real differences.
32. How do you compute total units and weighted average selling price for each purchased product?
Weight historical line prices by quantity and restrict the source to paid orders. The result has one row per product represented on paid-order lines.
SELECT i.product_id, SUM(i.quantity) AS units,
SUM(i.quantity * i.unit_price) * 1.0
/ NULLIF(SUM(i.quantity), 0) AS weighted_unit_price
FROM order_items AS i
JOIN orders AS o ON o.order_id = i.order_id
WHERE o.status = 'paid'
GROUP BY i.product_id
ORDER BY
SELECT i.product_id, SUM(i.quantity) AS units,
SUM(i.quantity * i.unit_price) * 1.0
/ NULLIF(SUM(i.quantity), 0) AS weighted_unit_price
FROM order_items AS i
JOIN orders AS o ON o.order_id = i.order_id
WHERE o.status = 'paid'
GROUP BY i.product_id
ORDER BY
SELECT i.product_id, SUM(i.quantity) AS units,
SUM(i.quantity * i.unit_price) * 1.0
/ NULLIF(SUM(i.quantity), 0) AS weighted_unit_price
FROM order_items AS i
JOIN orders AS o ON o.order_id = i.order_id
WHERE o.status = 'paid'
GROUP BY i.product_id
ORDER BY
An unweighted average of line prices would give a one-unit line the same influence as a hundred-unit line. Positive quantities make the denominator positive under this contract; NULLIF documents the intended safety rule if the model later permits reversals. Products with no qualifying lines do not appear. The result measures merchandise unit prices before any order-level adjustments, and combining different currencies would require grouping or conversion rules not present in this schema.
33. How do you find paid orders whose stored total differs from the sum of their lines?
Aggregate lines to order grain before comparing, and define a missing line set as a zero line sum. This query reports discrepancies, not necessarily errors: taxes or discounts may legitimately explain them.
SELECT o.order_id, o.total,
COALESCE(x.line_total, 0) AS line_total
FROM orders AS o
LEFT JOIN (
SELECT order_id, SUM(quantity * unit_price) AS line_total
FROM order_items GROUP BY order_id
) AS x ON x.order_id = o.order_id
WHERE o.status = 'paid'
AND o.total <> COALESCE(x.line_total, 0)
ORDER BY
SELECT o.order_id, o.total,
COALESCE(x.line_total, 0) AS line_total
FROM orders AS o
LEFT JOIN (
SELECT order_id, SUM(quantity * unit_price) AS line_total
FROM order_items GROUP BY order_id
) AS x ON x.order_id = o.order_id
WHERE o.status = 'paid'
AND o.total <> COALESCE(x.line_total, 0)
ORDER BY
SELECT o.order_id, o.total,
COALESCE(x.line_total, 0) AS line_total
FROM orders AS o
LEFT JOIN (
SELECT order_id, SUM(quantity * unit_price) AS line_total
FROM order_items GROUP BY order_id
) AS x ON x.order_id = o.order_id
WHERE o.status = 'paid'
AND o.total <> COALESCE(x.line_total, 0)
ORDER BY
A positive-total order without lines is flagged, while a zero-total order without lines is not. The shared PostgreSQL numeric types permit exact comparison for these stored amounts; floating-point schemas need an explicitly justified tolerance instead. Aggregating before the join makes one comparison per order and leaves room to add independent summaries without multiplying the line rows.
34. How do you find employees who earn more than their immediate manager?
Join an employee to their manager and compare the two salary values. The output contains one row per employee whose recorded salary is strictly greater than their manager's recorded salary.
SELECT e.employee_id, e.name, e.salary,
m.employee_id AS manager_id, m.salary AS manager_salary
FROM employees AS e
JOIN employees AS m ON m.employee_id = e.manager_id
WHERE e.salary > m.salary
ORDER BY
SELECT e.employee_id, e.name, e.salary,
m.employee_id AS manager_id, m.salary AS manager_salary
FROM employees AS e
JOIN employees AS m ON m.employee_id = e.manager_id
WHERE e.salary > m.salary
ORDER BY
SELECT e.employee_id, e.name, e.salary,
m.employee_id AS manager_id, m.salary AS manager_salary
FROM employees AS e
JOIN employees AS m ON m.employee_id = e.manager_id
WHERE e.salary > m.salary
ORDER BY
Employees without a manager are excluded by the inner join. If either salary is null, the comparison is unknown and the pair does not qualify; the query does not assume missing pay means zero. Equal salaries also do not qualify. The requirement concerns only the immediate manager, not every ancestor in a hierarchy. A self-manager data error compares the same salary with itself and is not returned, so this compensation query should not be mistaken for hierarchy validation.
35. How do you generate every department-and-status combination for a reporting template?
Cross-join the department dimension to a small explicit status domain. The result contains three rows per department, including combinations with no observed employees or orders; it is a scaffold rather than a measured report.
WITH statuses(status) AS (
VALUES ('paid'), ('pending'), ('canceled')
)
SELECT d.department_id, d.name, s.status
FROM departments AS d
CROSS JOIN statuses AS s
ORDER BY d.department_id,
WITH statuses(status) AS (
VALUES ('paid'), ('pending'), ('canceled')
)
SELECT d.department_id, d.name, s.status
FROM departments AS d
CROSS JOIN statuses AS s
ORDER BY d.department_id,
WITH statuses(status) AS (
VALUES ('paid'), ('pending'), ('canceled')
)
SELECT d.department_id, d.name, s.status
FROM departments AS d
CROSS JOIN statuses AS s
ORDER BY d.department_id,
The output grain is the department/status pair. If there are four departments, there are twelve rows; if the department table is empty, the result is empty. This example does not imply that orders belong to departments—the shared schema contains no such relationship. A real report must supply a valid mapping before attaching counts. Cross joins are useful for deliberate domain expansion, but accidental cross joins between fact tables can generate enormous, semantically meaningless results.
36. How do you identify unordered pairs of customers in the same known country exactly once?
Self-join customers on country and keep only increasing customer-ID pairs. This returns each pair once without self-pairs or reversed duplicates.
SELECT a.customer_id AS customer_a, b.customer_id AS customer_b,
a.country
FROM customers AS a
JOIN customers AS b
ON a.country = b.country AND a.customer_id < b.customer_id
WHERE a.country IS NOT NULL
ORDER BY a.customer_id,
SELECT a.customer_id AS customer_a, b.customer_id AS customer_b,
a.country
FROM customers AS a
JOIN customers AS b
ON a.country = b.country AND a.customer_id < b.customer_id
WHERE a.country IS NOT NULL
ORDER BY a.customer_id,
SELECT a.customer_id AS customer_a, b.customer_id AS customer_b,
a.country
FROM customers AS a
JOIN customers AS b
ON a.country = b.country AND a.customer_id < b.customer_id
WHERE a.country IS NOT NULL
ORDER BY a.customer_id,
Three customers in one country produce three pairs, not six directed pairs. The explicit null filter documents that unknown countries must not be treated as a shared location; ordinary equality already avoids matching two null values. Names are irrelevant to pair identity. A country containing many customers yields a quadratic number of pairs, so a correct query can still be too large for an interactive endpoint. If only country counts are required, aggregate instead of materializing every pair.
37. How do you find orders containing at least two distinct products rather than merely two lines?
Count distinct product IDs within each order. The result reports one row per qualifying order, with repeated lines for the same product contributing only one distinct product.
SELECT order_id, COUNT(DISTINCT product_id) AS distinct_products
FROM order_items
GROUP BY order_id
HAVING COUNT(DISTINCT product_id) >= 2
ORDER BY
SELECT order_id, COUNT(DISTINCT product_id) AS distinct_products
FROM order_items
GROUP BY order_id
HAVING COUNT(DISTINCT product_id) >= 2
ORDER BY
SELECT order_id, COUNT(DISTINCT product_id) AS distinct_products
FROM order_items
GROUP BY order_id
HAVING COUNT(DISTINCT product_id) >= 2
ORDER BY
Two lines referencing the same product do not qualify, while two lines referencing different products do. Orders without lines are absent, which is correct for this threshold. The product foreign key should be non-null for a strict line-item contract; if null product IDs are allowed, COUNT(DISTINCT product_id) ignores them rather than inventing a product. This query considers all order statuses because status is not stored on the line table and no paid-only requirement is stated. Add an orders join and filter if that restriction is required.
38. How do you list products that have never appeared on any order line?
Use an anti-existence test against line items. The output includes each catalog product for which no line has ever referenced its ID.
SELECT p.product_id, p.name
FROM products AS p
WHERE NOT EXISTS (
SELECT 1 FROM order_items AS i
WHERE i.product_id = p.product_id
)
ORDER BY
SELECT p.product_id, p.name
FROM products AS p
WHERE NOT EXISTS (
SELECT 1 FROM order_items AS i
WHERE i.product_id = p.product_id
)
ORDER BY
SELECT p.product_id, p.name
FROM products AS p
WHERE NOT EXISTS (
SELECT 1 FROM order_items AS i
WHERE i.product_id = p.product_id
)
ORDER BY
A product on a canceled order still counts as having appeared on a line and is therefore excluded. Finding products never sold on a paid order would require joining orders inside the subquery and applying the paid predicate. This version is robust to unrelated null product references in a looser import schema, unlike NOT IN over nullable values. An empty line table returns every product. The exercise tests absence at product grain rather than a revenue threshold: a product appearing on a zero-priced line is still present.
39. How do you find each customer's largest paid order amount while preserving customers without paid orders?
Use a left join limited to paid orders and calculate the maximum per customer. Keep null for customers without a qualifying order, because missing activity is different from a real zero-valued order.
SELECT c.customer_id, MAX(o.total) AS largest_paid_order
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id
ORDER BY
SELECT c.customer_id, MAX(o.total) AS largest_paid_order
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id
ORDER BY
SELECT c.customer_id, MAX(o.total) AS largest_paid_order
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id
ORDER BY
This returns an amount, not the identity of a particular order. If two orders tie at the maximum, the amount still appears once. A follow-up asking for the full largest-order row needs a tie policy: return all tied orders or choose one deterministically. Replacing null with zero can be appropriate for some dashboards, but then a separate count is needed if consumers must distinguish no purchase from a zero-value purchase. The current query preserves that distinction directly.
40. How do you verify that a customer-order join has not accidentally multiplied order rows?
Count joined rows for each order and flag counts different from one. The diagnostic should return no rows for the intended many-to-one customer join; any returned order ID has more than one matching joined row.
SELECT o.order_id, COUNT(*) AS joined_rows
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY o.order_id
HAVING COUNT(*) <> 1
ORDER BY
SELECT o.order_id, COUNT(*) AS joined_rows
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY o.order_id
HAVING COUNT(*) <> 1
ORDER BY
SELECT o.order_id, COUNT(*) AS joined_rows
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY o.order_id
HAVING COUNT(*) <> 1
ORDER BY
Under the primary-key contract, multiplication cannot occur in this exact join, so an empty result is expected. The pattern becomes useful when reviewing a modified join against a non-unique lookup or a staging table. It checks multiplication only: an inner join can silently lose unmatched orders, and those missing rows do not form groups here. Validate losses separately with an anti-join or a left-join count. Treat grain checks as explicit assertions, not as a reason to add DISTINCT until incorrect totals look plausible.
SQL Coding Interview Questions and Answers for Intermediate
These solutions combine query stages while keeping ties, partitions, duplicate activity, denominators, and calendar boundaries visible. A compact query is useful only if its intermediate rows represent the intended units.
41. How do you return exactly two highest-paid employees per department?
Rank employees with ROW_NUMBER() and retain rows 1–2. Exclude unknown salaries before ranking, and use the primary key to make equal salaries deterministic.
WITH ranked AS (
SELECT employee_id, department_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC, employee_id
) AS rn
FROM employees
WHERE salary IS NOT NULL
)
SELECT employee_id, department_id, salary
FROM ranked WHERE rn <= 2
ORDER BY department_id,
WITH ranked AS (
SELECT employee_id, department_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC, employee_id
) AS rn
FROM employees
WHERE salary IS NOT NULL
)
SELECT employee_id, department_id, salary
FROM ranked WHERE rn <= 2
ORDER BY department_id,
WITH ranked AS (
SELECT employee_id, department_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC, employee_id
) AS rn
FROM employees
WHERE salary IS NOT NULL
)
SELECT employee_id, department_id, salary
FROM ranked WHERE rn <= 2
ORDER BY department_id,
The output grain is one employee, with at most two rows per department; a department with one known salary returns one row. NULL department IDs form a separate partition rather than matching a department record. This answers an exact-headcount requirement, not “include everyone tied for second.” Do not add employee ID to a DENSE_RANK() ordering if the intended equivalence is equal salary: that would split ties. Filtering the window result requires an outer query because the same query level's WHERE runs before window evaluation.
42. How do you return every employee in the top two distinct salary bands per department?
Use DENSE_RANK() ordered by salary alone, then select the first two salary ranks. Unlike an exact-two query, this deliberately retains every employee sharing either salary value.
WITH ranked AS (
SELECT employee_id, department_id, salary,
DENSE_RANK() OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS salary_rank
FROM employees
WHERE salary IS NOT NULL
)
SELECT employee_id, department_id, salary, salary_rank
FROM ranked WHERE salary_rank <= 2
ORDER BY department_id, salary_rank,
WITH ranked AS (
SELECT employee_id, department_id, salary,
DENSE_RANK() OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS salary_rank
FROM employees
WHERE salary IS NOT NULL
)
SELECT employee_id, department_id, salary, salary_rank
FROM ranked WHERE salary_rank <= 2
ORDER BY department_id, salary_rank,
WITH ranked AS (
SELECT employee_id, department_id, salary,
DENSE_RANK() OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS salary_rank
FROM employees
WHERE salary IS NOT NULL
)
SELECT employee_id, department_id, salary, salary_rank
FROM ranked WHERE salary_rank <= 2
ORDER BY department_id, salary_rank,
For salaries 100, 100, 90, and 80, all three employees earning 100 or 90 qualify. RANK() instead produces 1, 1, 3, 4 and would omit the 90 salary under the same cutoff. Unknown salaries are excluded rather than ranked as values; departments with fewer than two distinct salaries contribute whatever they have. The final employee-ID ordering stabilizes display without changing rank equivalence. A correct interview answer first clarifies whether the business means two people, two competition positions, or two distinct compensation levels.
43. How do you find the second distinct highest salary in each department?
Assign dense salary ranks and collapse the selected second band to one row per department. This formulation returns only departments with at least two different non-NULL salaries.
WITH ranked AS (
SELECT department_id, salary,
DENSE_RANK() OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS salary_rank
FROM employees
WHERE salary IS NOT NULL
)
SELECT department_id, MAX(salary) AS second_salary
FROM ranked
WHERE salary_rank = 2
GROUP BY department_id
ORDER BY
WITH ranked AS (
SELECT department_id, salary,
DENSE_RANK() OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS salary_rank
FROM employees
WHERE salary IS NOT NULL
)
SELECT department_id, MAX(salary) AS second_salary
FROM ranked
WHERE salary_rank = 2
GROUP BY department_id
ORDER BY
WITH ranked AS (
SELECT department_id, salary,
DENSE_RANK() OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS salary_rank
FROM employees
WHERE salary IS NOT NULL
)
SELECT department_id, MAX(salary) AS second_salary
FROM ranked
WHERE salary_rank = 2
GROUP BY department_id
ORDER BY
Multiple employees may occupy the second band, so selecting their rows without aggregation would violate the requested department-level grain. MAX is safe here because every qualifying salary within a department is equal. If the output must include departments with no second salary, left join this result to departments and return NULL for the missing value. A NULL department partition is an unassigned bucket, not an actual department. OFFSET 1 on un-deduplicated employee salaries would answer the second employee instead and is wrong when the highest salary is tied.
44. How do you compute each paid order's share of its customer's paid revenue?
Divide each paid order total by a partition-wide sum. Filter to paid orders before the window calculation so both numerator and denominator describe the same population.
SELECT order_id, customer_id, total,
100.0 * total / NULLIF(
SUM(total) OVER (PARTITION BY customer_id), 0
) AS revenue_percent
FROM orders
WHERE status = 'paid'
ORDER BY customer_id,
SELECT order_id, customer_id, total,
100.0 * total / NULLIF(
SUM(total) OVER (PARTITION BY customer_id), 0
) AS revenue_percent
FROM orders
WHERE status = 'paid'
ORDER BY customer_id,
SELECT order_id, customer_id, total,
100.0 * total / NULLIF(
SUM(total) OVER (PARTITION BY customer_id), 0
) AS revenue_percent
FROM orders
WHERE status = 'paid'
ORDER BY customer_id,
The result retains one row per paid order rather than collapsing customers with GROUP BY. NULLIF makes a zero-revenue customer's percentages unknown instead of raising division by zero; zero-valued orders can exist under the shared schema. The decimal constant prevents integer-division surprises in engines or fixtures that use integer amounts. Do not round before calculating or expect separately rounded percentages to add to exactly 100. Customers without paid orders produce no rows, and joining item lines before the window would duplicate order totals and distort the denominator unless the query restored order grain first.
45. How do you calculate a deterministic running paid-revenue total for each customer?
Use a row-based window ordered by timestamp and a unique tie-breaker. Explicit ROWS semantics make each order advance the balance individually even when several orders share a timestamp.
SELECT order_id, customer_id, ordered_at, total,
SUM(total) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_revenue
FROM orders
WHERE status = 'paid'
ORDER BY customer_id, ordered_at,
SELECT order_id, customer_id, ordered_at, total,
SUM(total) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_revenue
FROM orders
WHERE status = 'paid'
ORDER BY customer_id, ordered_at,
SELECT order_id, customer_id, ordered_at, total,
SUM(total) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_revenue
FROM orders
WHERE status = 'paid'
ORDER BY customer_id, ordered_at,
Each output row represents a paid order; the last value within a customer equals that customer's paid revenue. An ordering by timestamp alone would leave same-time rows under-specified for row-by-row accumulation. The default frame for an ordered aggregate window can include the current row's peers, yielding a jump across equal timestamps rather than the desired per-order increments. The order ID is a deterministic convention, not proof that one simultaneous order happened earlier. For reporting periods, decide whether to filter before accumulation or accumulate history first and filter afterward; those produce different opening balances.
46. How do you compare each paid order with the customer's previous paid order amount?
Apply LAG(total) within each customer's paid orders, and calculate the difference in an outer query. Define ordering by timestamp and order ID so equal timestamps cannot change the selected predecessor unpredictably.
WITH previous AS (
SELECT order_id, customer_id, ordered_at, total,
LAG(total) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS previous_total
FROM orders WHERE status = 'paid'
)
SELECT order_id, customer_id, total, previous_total,
total - previous_total AS amount_change
FROM previous
ORDER BY customer_id, ordered_at,
WITH previous AS (
SELECT order_id, customer_id, ordered_at, total,
LAG(total) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS previous_total
FROM orders WHERE status = 'paid'
)
SELECT order_id, customer_id, total, previous_total,
total - previous_total AS amount_change
FROM previous
ORDER BY customer_id, ordered_at,
WITH previous AS (
SELECT order_id, customer_id, ordered_at, total,
LAG(total) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS previous_total
FROM orders WHERE status = 'paid'
)
SELECT order_id, customer_id, total, previous_total,
total - previous_total AS amount_change
FROM previous
ORDER BY customer_id, ordered_at,
The first paid order returns NULL for both the previous total and the difference, accurately representing no predecessor. A default of zero would instead invent a prior zero-amount order and change the meaning. Filtering to paid orders inside the CTE makes “previous” mean previous paid order, not previous order of any status. This is a row comparison, not a comparison with yesterday: gaps between purchases do not create rows. If timestamps are simultaneous, the ID tie-breaker supplies a stable sequence but does not infer physical event causality.
47. How do you identify the first paid order for every customer?
Assign row numbers over each customer's paid orders in ascending chronological order, then keep row 1. The order ID selects a single deterministic winner when timestamps tie.
WITH first_orders AS (
SELECT order_id, customer_id, ordered_at, total,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS rn
FROM orders WHERE status = 'paid'
)
SELECT customer_id, order_id, ordered_at, total
FROM first_orders WHERE rn = 1
ORDER BY
WITH first_orders AS (
SELECT order_id, customer_id, ordered_at, total,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS rn
FROM orders WHERE status = 'paid'
)
SELECT customer_id, order_id, ordered_at, total
FROM first_orders WHERE rn = 1
ORDER BY
WITH first_orders AS (
SELECT order_id, customer_id, ordered_at, total,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS rn
FROM orders WHERE status = 'paid'
)
SELECT customer_id, order_id, ordered_at, total
FROM first_orders WHERE rn = 1
ORDER BY
The output contains one row for each customer who has a paid order; customers with none are absent. Computing MIN(ordered_at) and joining back can return several rows when timestamps tie, while independently taking MIN(order_id) need not select the same order as the earliest timestamp. The window preserves all columns from the chosen record. This query defines “first” over the complete available history. Applying a reporting-date filter inside the CTE would instead find the first paid order within that period and could misclassify an existing customer as newly acquired.
48. How do you attach each customer's final paid-order amount to all their paid orders?
Use LAST_VALUE() over an explicitly complete partition. Without an ending boundary of UNBOUNDED FOLLOWING, an ordered window's default frame often ends at the current peer group rather than the last order.
SELECT order_id, customer_id, total,
LAST_VALUE(total) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS final_paid_order_total
FROM orders
WHERE status = 'paid'
ORDER BY customer_id, ordered_at,
SELECT order_id, customer_id, total,
LAST_VALUE(total) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS final_paid_order_total
FROM orders
WHERE status = 'paid'
ORDER BY customer_id, ordered_at,
SELECT order_id, customer_id, total,
LAST_VALUE(total) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS final_paid_order_total
FROM orders
WHERE status = 'paid'
ORDER BY customer_id, ordered_at,
The result still has one row per paid order, but the attached value is constant within each customer. The unique order ID resolves timestamp ties. “Final” means latest in the queried snapshot, not a promise that no future order will arrive. Because total is non-NULL in the contract, the latest value is always a known amount; with nullable inputs, PostgreSQL's LAST_VALUE does not silently skip NULLs. If only the last order row is needed, descending ROW_NUMBER() is a clearer fit than repeating a final value across every historical row.
49. How do you calculate a moving average over the current and previous two paid orders?
Specify a three-row frame rather than a three-day interval. The window includes up to two previous paid orders for the customer, regardless of how far apart their timestamps are.
SELECT order_id, customer_id, ordered_at, total,
AVG(total) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS average_last_three_orders,
COUNT(*) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS contributing_orders
FROM orders WHERE status = 'paid'
ORDER BY customer_id, ordered_at,
SELECT order_id, customer_id, ordered_at, total,
AVG(total) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS average_last_three_orders,
COUNT(*) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS contributing_orders
FROM orders WHERE status = 'paid'
ORDER BY customer_id, ordered_at,
SELECT order_id, customer_id, ordered_at, total,
AVG(total) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS average_last_three_orders,
COUNT(*) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS contributing_orders
FROM orders WHERE status = 'paid'
ORDER BY customer_id, ordered_at,
The first order averages one observation, the second averages two, and subsequent rows average three. Returning the frame count makes this warm-up behavior explicit. If the requirement demands exactly three observations, wrap the query and filter the count to three rather than treating missing history as zero-valued purchases. Timestamp and ID provide a total ordering, and NULL totals are forbidden by the shared schema. This moving average weights orders equally, not days or quantities; that distinction matters when purchase frequency varies substantially across customers.
50. How do you assign employees to four approximately equal-sized salary groups?
Use NTILE(4) after excluding unknown salaries. The buckets balance employee counts; they do not guarantee that equal salaries stay together or that each bucket covers an equal salary range.
SELECT employee_id, salary,
NTILE(4) OVER (ORDER BY salary DESC, employee_id) AS salary_bucket
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary_bucket, salary DESC,
SELECT employee_id, salary,
NTILE(4) OVER (ORDER BY salary DESC, employee_id) AS salary_bucket
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary_bucket, salary DESC,
SELECT employee_id, salary,
NTILE(4) OVER (ORDER BY salary DESC, employee_id) AS salary_bucket
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary_bucket, salary DESC,
The highest salaries occupy bucket 1, and employee ID makes assignments deterministic at ties. If the population is not divisible by four, earlier buckets receive the extra rows. With fewer than four employees, some bucket numbers have no rows rather than manufacturing empty employee records. This is a global partition; add PARTITION BY department_id only if each department needs its own grouping. A tied salary can straddle a boundary, so dense salary ranking or explicit threshold comparisons are more suitable when the policy requires identical salaries to receive identical labels. Clarify whether the interviewer means quantile-style headcounts or business-defined compensation bands.
51. How do you retain the newest event from each duplicate business-key group without deleting data?
Treat (customer_id, occurred_at, event_type) as the exercise's duplicate key and retain the largest event ID in each group. This is a read-only deduplicated view, not a mutation of the source.
WITH ranked AS (
SELECT event_id, customer_id, occurred_at, event_type,
ROW_NUMBER() OVER (
PARTITION BY customer_id, occurred_at, event_type
ORDER BY event_id DESC
) AS rn
FROM events
)
SELECT event_id, customer_id, occurred_at, event_type
FROM ranked WHERE rn = 1
ORDER BY customer_id, occurred_at,
WITH ranked AS (
SELECT event_id, customer_id, occurred_at, event_type,
ROW_NUMBER() OVER (
PARTITION BY customer_id, occurred_at, event_type
ORDER BY event_id DESC
) AS rn
FROM events
)
SELECT event_id, customer_id, occurred_at, event_type
FROM ranked WHERE rn = 1
ORDER BY customer_id, occurred_at,
WITH ranked AS (
SELECT event_id, customer_id, occurred_at, event_type,
ROW_NUMBER() OVER (
PARTITION BY customer_id, occurred_at, event_type
ORDER BY event_id DESC
) AS rn
FROM events
)
SELECT event_id, customer_id, occurred_at, event_type
FROM ranked WHERE rn = 1
ORDER BY customer_id, occurred_at,
The output grain is one chosen record per business key. “Newest” here explicitly means greatest ID, which is only an agreed ingestion-order proxy; it is not inferred from the event timestamp because that timestamp belongs to the duplicate key. Real systems may require a separate received-at column and tie-breaker. Do not deduplicate on customer and event type alone: legitimate repeated actions would disappear. All key columns are non-NULL under this exercise's input assumptions; if nullable keys are introduced, window partitioning groups NULL key values together, which must match the intended equivalence policy.
52. How do you build a paid-order amount histogram that retains empty buckets?
Define nonoverlapping half-open amount buckets, then left join paid orders into them. The bucket relation drives the result, so a range with no orders still appears with count zero.
WITH buckets(bucket, lower_bound, upper_bound) AS (
VALUES ('under 50', 0, 50), ('50 to under 100', 50, 100),
('100 and above', 100, NULL)
)
SELECT b.bucket, COUNT(o.order_id) AS paid_orders
FROM buckets AS b
LEFT JOIN orders AS o
ON o.status = 'paid' AND o.total >= b.lower_bound
AND (b.upper_bound IS NULL OR o.total < b.upper_bound)
GROUP BY b.bucket, b.lower_bound
ORDER BY
WITH buckets(bucket, lower_bound, upper_bound) AS (
VALUES ('under 50', 0, 50), ('50 to under 100', 50, 100),
('100 and above', 100, NULL)
)
SELECT b.bucket, COUNT(o.order_id) AS paid_orders
FROM buckets AS b
LEFT JOIN orders AS o
ON o.status = 'paid' AND o.total >= b.lower_bound
AND (b.upper_bound IS NULL OR o.total < b.upper_bound)
GROUP BY b.bucket, b.lower_bound
ORDER BY
WITH buckets(bucket, lower_bound, upper_bound) AS (
VALUES ('under 50', 0, 50), ('50 to under 100', 50, 100),
('100 and above', 100, NULL)
)
SELECT b.bucket, COUNT(o.order_id) AS paid_orders
FROM buckets AS b
LEFT JOIN orders AS o
ON o.status = 'paid' AND o.total >= b.lower_bound
AND (b.upper_bound IS NULL OR o.total < b.upper_bound)
GROUP BY b.bucket, b.lower_bound
ORDER BY
The output contains exactly three rows, including zero counts when the order table is empty. An amount of 50 belongs only to the middle bucket, and 100 belongs only to the last; inclusive upper bounds would double-count boundary values. NULL is an explicit unbounded-upper-limit marker in the bucket definition, not a missing order amount. Totals are nonnegative and non-NULL under the shared contract, so the buckets cover every paid order. COUNT(o.order_id) avoids counting the synthetic outer-join row. For configurable buckets, validate that ranges neither overlap nor leave unintended gaps before trusting the histogram.
53. How do you find customers who bought every product in a required product set?
Use double NOT EXISTS: a customer qualifies when no required product is missing from their paid-order purchases. The required set is explicit, and duplicates in order items do not affect existence.
WITH required(product_id) AS (VALUES (10), (20))
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM required AS r
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.customer_id = c.customer_id
AND o.status = 'paid'
AND i.product_id = r.product_id
)
)
ORDER BY
WITH required(product_id) AS (VALUES (10), (20))
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM required AS r
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.customer_id = c.customer_id
AND o.status = 'paid'
AND i.product_id = r.product_id
)
)
ORDER BY
WITH required(product_id) AS (VALUES (10), (20))
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM required AS r
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.customer_id = c.customer_id
AND o.status = 'paid'
AND i.product_id = r.product_id
)
)
ORDER BY
The output contains each qualifying customer once, including customers who bought additional products. Required IDs are assumed valid product identifiers; a nonexistent required product makes qualification impossible. An empty required set causes every customer to qualify through vacuous truth, including customers with no orders. If the business expects nobody when requirements are empty, add an outer EXISTS (SELECT 1 FROM required) guard. Counting joined rows without DISTINCT is unsafe because multiple purchases of one required product can falsely look like coverage of several products.
54. How do you find customers whose purchased-product set is exactly the required set?
Require coverage of every required product and reject any paid purchase outside that set. Set equality has two directions; coverage alone allows extra products.
WITH required(product_id) AS (VALUES (10), (20)),
bought AS (
SELECT DISTINCT o.customer_id, i.product_id
FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid'
)
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM required AS r
WHERE NOT EXISTS (
SELECT 1 FROM bought AS b
WHERE b.customer_id = c.customer_id AND b.product_id = r.product_id
)
)
AND NOT EXISTS (
SELECT 1 FROM bought AS b
WHERE b.customer_id = c.customer_id
AND NOT EXISTS (SELECT 1 FROM required AS r WHERE r.product_id = b.product_id)
)
ORDER BY
WITH required(product_id) AS (VALUES (10), (20)),
bought AS (
SELECT DISTINCT o.customer_id, i.product_id
FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid'
)
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM required AS r
WHERE NOT EXISTS (
SELECT 1 FROM bought AS b
WHERE b.customer_id = c.customer_id AND b.product_id = r.product_id
)
)
AND NOT EXISTS (
SELECT 1 FROM bought AS b
WHERE b.customer_id = c.customer_id
AND NOT EXISTS (SELECT 1 FROM required AS r WHERE r.product_id = b.product_id)
)
ORDER BY
WITH required(product_id) AS (VALUES (10), (20)),
bought AS (
SELECT DISTINCT o.customer_id, i.product_id
FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid'
)
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM required AS r
WHERE NOT EXISTS (
SELECT 1 FROM bought AS b
WHERE b.customer_id = c.customer_id AND b.product_id = r.product_id
)
)
AND NOT EXISTS (
SELECT 1 FROM bought AS b
WHERE b.customer_id = c.customer_id
AND NOT EXISTS (SELECT 1 FROM required AS r WHERE r.product_id = b.product_id)
)
ORDER BY
The result has one row per qualifying customer and ignores quantities and repeated purchases. Product IDs are non-NULL, so ordinary equality matches the set semantics cleanly. For an empty requirement set, only customers with no purchased products qualify. Paid orders with no item lines add no products, while canceled orders do not affect membership. The CTE makes the intended customer-product grain visible; it is not a guarantee that PostgreSQL materializes the intermediate result or that this formulation is optimal for every data distribution.
55. How do you calculate daily paid revenue in UTC rather than the session time zone?
Convert the timestamp to UTC local time before casting to a date, then group by that date. This makes the reporting boundary independent of the database connection's time-zone setting.
SELECT (ordered_at AT TIME ZONE 'UTC')::date AS revenue_day,
SUM(total) AS paid_revenue,
COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY (ordered_at AT TIME ZONE 'UTC')::date
ORDER BY
SELECT (ordered_at AT TIME ZONE 'UTC')::date AS revenue_day,
SUM(total) AS paid_revenue,
COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY (ordered_at AT TIME ZONE 'UTC')::date
ORDER BY
SELECT (ordered_at AT TIME ZONE 'UTC')::date AS revenue_day,
SUM(total) AS paid_revenue,
COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY (ordered_at AT TIME ZONE 'UTC')::date
ORDER BY
The PostgreSQL-specific AT TIME ZONE expression converts each timestamptz instant to a timestamp without time zone in the named zone; the cast then extracts the intended calendar date. Each output row represents an observed UTC day, not every day in a reporting range. Empty days are omitted, and total is summed once per order without a line-item join. If a business uses a regional reporting zone, replace UTC with an explicit IANA zone and accept that local calendar days can contain 23 or 25 hours. A plain ordered_at::date would silently depend on the session time zone.
56. How do you include zero-revenue days in a fixed seven-day report?
Construct a seven-day date spine, aggregate orders to day grain, and left join the two. The date spine determines which days appear, so a day with no paid orders receives zero rather than vanishing.
WITH days AS (
SELECT DATE '2026-01-01' + n AS day
FROM generate_series(0, 6) AS g(n)
), daily AS (
SELECT (ordered_at AT TIME ZONE 'UTC')::date AS day,
SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
AND ordered_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
AND ordered_at < TIMESTAMPTZ '2026-01-08 00:00:00+00'
GROUP BY (ordered_at AT TIME ZONE 'UTC')::date
)
SELECT d.day, COALESCE(x.revenue, 0) AS revenue
FROM days AS d LEFT JOIN daily AS x ON x.day = d.day
ORDER BY
WITH days AS (
SELECT DATE '2026-01-01' + n AS day
FROM generate_series(0, 6) AS g(n)
), daily AS (
SELECT (ordered_at AT TIME ZONE 'UTC')::date AS day,
SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
AND ordered_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
AND ordered_at < TIMESTAMPTZ '2026-01-08 00:00:00+00'
GROUP BY (ordered_at AT TIME ZONE 'UTC')::date
)
SELECT d.day, COALESCE(x.revenue, 0) AS revenue
FROM days AS d LEFT JOIN daily AS x ON x.day = d.day
ORDER BY
WITH days AS (
SELECT DATE '2026-01-01' + n AS day
FROM generate_series(0, 6) AS g(n)
), daily AS (
SELECT (ordered_at AT TIME ZONE 'UTC')::date AS day,
SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
AND ordered_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
AND ordered_at < TIMESTAMPTZ '2026-01-08 00:00:00+00'
GROUP BY (ordered_at AT TIME ZONE 'UTC')::date
)
SELECT d.day, COALESCE(x.revenue, 0) AS revenue
FROM days AS d LEFT JOIN daily AS x ON x.day = d.day
ORDER BY
This PostgreSQL example uses generate_series, typed date literals, and time-zone conversion. The upper bound is exclusive, preventing midnight on January 8 from leaking into the report. There are exactly seven output rows even when the orders table is empty. Aggregating before the join avoids date-spine duplication. Zero is appropriate because the measure is revenue from known qualifying rows; it should not be used to hide a missing data feed or an unknown amount in a different schema.
57. How do you calculate a seven-calendar-day moving revenue total including empty days?
Build a complete daily series before using a seven-row window, and include six preceding days of input before the requested output range. Otherwise seven rows might represent seven active days rather than seven calendar days.
WITH days AS (
SELECT DATE '2025-12-26' + n AS day
FROM generate_series(0, 12) AS g(n)
), daily AS (
SELECT (ordered_at AT TIME ZONE 'UTC')::date AS day, SUM(total) AS revenue
FROM orders WHERE status = 'paid'
GROUP BY (ordered_at AT TIME ZONE 'UTC')::date
), rolling AS (
SELECT d.day,
SUM(COALESCE(x.revenue, 0)) OVER (
ORDER BY d.day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS revenue_7d
FROM days AS d LEFT JOIN daily AS x ON x.day = d.day
)
SELECT day, revenue_7d FROM rolling
WHERE day >= DATE '2026-01-01'
ORDER BY day
WITH days AS (
SELECT DATE '2025-12-26' + n AS day
FROM generate_series(0, 12) AS g(n)
), daily AS (
SELECT (ordered_at AT TIME ZONE 'UTC')::date AS day, SUM(total) AS revenue
FROM orders WHERE status = 'paid'
GROUP BY (ordered_at AT TIME ZONE 'UTC')::date
), rolling AS (
SELECT d.day,
SUM(COALESCE(x.revenue, 0)) OVER (
ORDER BY d.day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS revenue_7d
FROM days AS d LEFT JOIN daily AS x ON x.day = d.day
)
SELECT day, revenue_7d FROM rolling
WHERE day >= DATE '2026-01-01'
ORDER BY day
WITH days AS (
SELECT DATE '2025-12-26' + n AS day
FROM generate_series(0, 12) AS g(n)
), daily AS (
SELECT (ordered_at AT TIME ZONE 'UTC')::date AS day, SUM(total) AS revenue
FROM orders WHERE status = 'paid'
GROUP BY (ordered_at AT TIME ZONE 'UTC')::date
), rolling AS (
SELECT d.day,
SUM(COALESCE(x.revenue, 0)) OVER (
ORDER BY d.day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS revenue_7d
FROM days AS d LEFT JOIN daily AS x ON x.day = d.day
)
SELECT day, revenue_7d FROM rolling
WHERE day >= DATE '2026-01-01'
ORDER BY day
The PostgreSQL date spine spans December 26 through January 7, but only January 1–7 are displayed. Each output is an inclusive current-day-plus-six-days sum in UTC. Filtering the spine to January before windowing would truncate the first six output windows. Empty days correctly contribute zero. For large histories, restrict the daily aggregation to the complete input interval with half-open timestamp predicates; filtering only the final report interval would sacrifice correctness for apparent speed.
58. How do you compare January paid revenue with December and avoid division by zero?
Aggregate the two months separately, fill missing months with zero, and calculate the percentage change only when the prior amount is nonzero. PostgreSQL's UTC month conversion gives stable calendar boundaries.
WITH monthly AS (
SELECT date_trunc('month', ordered_at AT TIME ZONE 'UTC')::date AS month,
SUM(total) AS revenue
FROM orders WHERE status = 'paid'
GROUP BY date_trunc('month', ordered_at AT TIME ZONE 'UTC')::date
), amounts AS (
SELECT COALESCE(SUM(CASE WHEN month = DATE '2025-12-01' THEN revenue END), 0) AS prior,
COALESCE(SUM(CASE WHEN month = DATE '2026-01-01' THEN revenue END), 0) AS current
FROM monthly
)
SELECT prior, current, current - prior AS absolute_change,
100.0 * (current - prior) / NULLIF(prior, 0) AS percent_change
FROM
WITH monthly AS (
SELECT date_trunc('month', ordered_at AT TIME ZONE 'UTC')::date AS month,
SUM(total) AS revenue
FROM orders WHERE status = 'paid'
GROUP BY date_trunc('month', ordered_at AT TIME ZONE 'UTC')::date
), amounts AS (
SELECT COALESCE(SUM(CASE WHEN month = DATE '2025-12-01' THEN revenue END), 0) AS prior,
COALESCE(SUM(CASE WHEN month = DATE '2026-01-01' THEN revenue END), 0) AS current
FROM monthly
)
SELECT prior, current, current - prior AS absolute_change,
100.0 * (current - prior) / NULLIF(prior, 0) AS percent_change
FROM
WITH monthly AS (
SELECT date_trunc('month', ordered_at AT TIME ZONE 'UTC')::date AS month,
SUM(total) AS revenue
FROM orders WHERE status = 'paid'
GROUP BY date_trunc('month', ordered_at AT TIME ZONE 'UTC')::date
), amounts AS (
SELECT COALESCE(SUM(CASE WHEN month = DATE '2025-12-01' THEN revenue END), 0) AS prior,
COALESCE(SUM(CASE WHEN month = DATE '2026-01-01' THEN revenue END), 0) AS current
FROM monthly
)
SELECT prior, current, current - prior AS absolute_change,
100.0 * (current - prior) / NULLIF(prior, 0) AS percent_change
FROM
The output is exactly one comparison row, even with no orders. A zero December value makes relative growth undefined, so the percentage is NULL rather than an invented 100 percent. Absolute change remains meaningful. The query compares complete fixed months; it does not compare a partial current month with a full previous month. A production report should also state whether late-arriving orders can restate history and whether the measure is order revenue, cash receipts, or another metric.
59. How do you find consecutive integer activity-day streaks after removing duplicate days?
Deduplicate each user's day numbers, subtract a row number from each ordered day, and group by the resulting island key. Consecutive integers advance in step with row numbers, so their difference remains constant.
WITH activity(customer_id, day_no) AS (
VALUES (1, 1), (1, 2), (1, 2), (1, 4), (1, 5), (2, 8)
), distinct_days AS (
SELECT DISTINCT customer_id, day_no FROM activity
), numbered AS (
SELECT customer_id, day_no,
day_no - ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY day_no
) AS island
FROM distinct_days
)
SELECT customer_id, MIN(day_no) AS start_day,
MAX(day_no) AS end_day, COUNT(*) AS streak_length
FROM numbered GROUP BY customer_id, island
ORDER BY customer_id,
WITH activity(customer_id, day_no) AS (
VALUES (1, 1), (1, 2), (1, 2), (1, 4), (1, 5), (2, 8)
), distinct_days AS (
SELECT DISTINCT customer_id, day_no FROM activity
), numbered AS (
SELECT customer_id, day_no,
day_no - ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY day_no
) AS island
FROM distinct_days
)
SELECT customer_id, MIN(day_no) AS start_day,
MAX(day_no) AS end_day, COUNT(*) AS streak_length
FROM numbered GROUP BY customer_id, island
ORDER BY customer_id,
WITH activity(customer_id, day_no) AS (
VALUES (1, 1), (1, 2), (1, 2), (1, 4), (1, 5), (2, 8)
), distinct_days AS (
SELECT DISTINCT customer_id, day_no FROM activity
), numbered AS (
SELECT customer_id, day_no,
day_no - ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY day_no
) AS island
FROM distinct_days
)
SELECT customer_id, MIN(day_no) AS start_day,
MAX(day_no) AS end_day, COUNT(*) AS streak_length
FROM numbered GROUP BY customer_id, island
ORDER BY customer_id,
The fixture returns customer 1's streaks 1–2 and 4–5, both length two, plus customer 2's one-day streak. Day numbers are non-NULL integer offsets from an agreed calendar origin, not elapsed 24-hour timestamp buckets. Duplicate removal is essential because repeated days would consume row numbers and split a valid streak. The technique assumes adjacency means a difference of exactly one; business-day calendars or tolerated gaps require an explicitly different rule.
60. How do you report missing integer day numbers between two endpoints?
Generate the expected domain and use an anti-existence predicate against observed days. PostgreSQL's generate_series creates inclusive integer endpoints without depending on the observed table containing either boundary.
WITH observed(day_no) AS (VALUES (1), (2), (2), (5)),
expected AS (
SELECT n AS day_no FROM generate_series(1, 7) AS g(n)
)
SELECT e.day_no
FROM expected AS e
WHERE NOT EXISTS (
SELECT 1 FROM observed AS o WHERE o.day_no = e.day_no
)
ORDER BY
WITH observed(day_no) AS (VALUES (1), (2), (2), (5)),
expected AS (
SELECT n AS day_no FROM generate_series(1, 7) AS g(n)
)
SELECT e.day_no
FROM expected AS e
WHERE NOT EXISTS (
SELECT 1 FROM observed AS o WHERE o.day_no = e.day_no
)
ORDER BY
WITH observed(day_no) AS (VALUES (1), (2), (2), (5)),
expected AS (
SELECT n AS day_no FROM generate_series(1, 7) AS g(n)
)
SELECT e.day_no
FROM expected AS e
WHERE NOT EXISTS (
SELECT 1 FROM observed AS o WHERE o.day_no = e.day_no
)
ORDER BY
The output is 3, 4, 6, and 7. Duplicate observed values have no effect, and a NULL observed value would not poison the predicate as it can with NOT IN. Empty observations return all expected days. This query reports individual missing values, not compressed missing ranges; those can be grouped into islands afterward when needed. The endpoints are the reporting contract, so deriving them from MIN and MAX of observations would fail to detect missing leading or trailing days. For calendar reporting, map these offsets to explicit dates in the desired reporting time zone.
61. How do you assign customers to first-purchase-month cohorts and count cohort sizes?
Find each customer's first paid-order timestamp across all available history, convert it to a UTC month, and count customers at that cohort grain. Do not count orders as cohort members.
WITH first_purchase AS (
SELECT customer_id, MIN(ordered_at) AS first_paid_at
FROM orders WHERE status = 'paid'
GROUP BY customer_id
)
SELECT date_trunc('month', first_paid_at AT TIME ZONE 'UTC')::date AS cohort_month,
COUNT(*) AS customers
FROM first_purchase
GROUP BY date_trunc('month', first_paid_at AT TIME ZONE 'UTC')::date
ORDER BY
WITH first_purchase AS (
SELECT customer_id, MIN(ordered_at) AS first_paid_at
FROM orders WHERE status = 'paid'
GROUP BY customer_id
)
SELECT date_trunc('month', first_paid_at AT TIME ZONE 'UTC')::date AS cohort_month,
COUNT(*) AS customers
FROM first_purchase
GROUP BY date_trunc('month', first_paid_at AT TIME ZONE 'UTC')::date
ORDER BY
WITH first_purchase AS (
SELECT customer_id, MIN(ordered_at) AS first_paid_at
FROM orders WHERE status = 'paid'
GROUP BY customer_id
)
SELECT date_trunc('month', first_paid_at AT TIME ZONE 'UTC')::date AS cohort_month,
COUNT(*) AS customers
FROM first_purchase
GROUP BY date_trunc('month', first_paid_at AT TIME ZONE 'UTC')::date
ORDER BY
The PostgreSQL expression uses an explicit zone and returns one row per observed acquisition month. Each purchasing customer contributes exactly once regardless of order count or equal first-order timestamps. Customers without paid orders have no purchase cohort and are absent. A reporting restriction applied before MIN would turn “first ever” into “first visible in this period” and contaminate the cohort definition. If historical ingestion begins partway through the business's lifetime, the query measures first observed purchase rather than proven acquisition. Signup cohorts require a signup timestamp that the shared customer schema does not provide.
62. How do you calculate the average paid-order value per purchasing customer and then average those customer averages?
Aggregate paid orders to customer grain first, then average those averages. This gives each purchasing customer equal weight, unlike averaging all orders directly.
WITH customer_averages AS (
SELECT customer_id, AVG(total) AS average_order_value,
COUNT(*) AS paid_order_count
FROM orders WHERE status = 'paid'
GROUP BY customer_id
)
SELECT AVG(average_order_value) AS mean_customer_average,
COUNT(*) AS purchasing_customers,
SUM(paid_order_count) AS paid_orders
FROM
WITH customer_averages AS (
SELECT customer_id, AVG(total) AS average_order_value,
COUNT(*) AS paid_order_count
FROM orders WHERE status = 'paid'
GROUP BY customer_id
)
SELECT AVG(average_order_value) AS mean_customer_average,
COUNT(*) AS purchasing_customers,
SUM(paid_order_count) AS paid_orders
FROM
WITH customer_averages AS (
SELECT customer_id, AVG(total) AS average_order_value,
COUNT(*) AS paid_order_count
FROM orders WHERE status = 'paid'
GROUP BY customer_id
)
SELECT AVG(average_order_value) AS mean_customer_average,
COUNT(*) AS purchasing_customers,
SUM(paid_order_count) AS paid_orders
FROM
For one customer with one 100-unit order and another with three 20-unit orders, this metric is 60, while the order-weighted average is 40. The two answers address different business questions, so neither should be chosen accidentally. Customers with no paid orders are excluded rather than assigned a fabricated average of zero. If no customers qualify, the outer count is zero and the averages and summed order count are NULL; use COALESCE only if a reporting contract explicitly wants another representation. Monetary totals are numeric in PostgreSQL, preserving decimal arithmetic before any presentation rounding.
63. How do you list paid orders above their own customer's average without collapsing the order rows?
Calculate the customer-level average as a window value, then filter in an outer query. The comparison is strictly greater, so orders equal to the average do not qualify.
WITH scored AS (
SELECT order_id, customer_id, total,
AVG(total) OVER (PARTITION BY customer_id) AS customer_average
FROM orders WHERE status = 'paid'
)
SELECT order_id, customer_id, total, customer_average
FROM scored
WHERE total > customer_average
ORDER BY customer_id,
WITH scored AS (
SELECT order_id, customer_id, total,
AVG(total) OVER (PARTITION BY customer_id) AS customer_average
FROM orders WHERE status = 'paid'
)
SELECT order_id, customer_id, total, customer_average
FROM scored
WHERE total > customer_average
ORDER BY customer_id,
WITH scored AS (
SELECT order_id, customer_id, total,
AVG(total) OVER (PARTITION BY customer_id) AS customer_average
FROM orders WHERE status = 'paid'
)
SELECT order_id, customer_id, total, customer_average
FROM scored
WHERE total > customer_average
ORDER BY customer_id,
The output grain remains one qualifying paid order. The average includes the current order; it is not a leave-one-out baseline. A customer with one order has no result because that order equals its own average, and a customer with equal-valued orders also has none. Because totals are non-NULL, every paid order contributes to its customer's denominator. Filtering by amount inside the CTE would change the population used to compute the average, creating a different and potentially circular benchmark. When joining additional tables, ensure there remains exactly one input row per order before evaluating the window.
64. How do you report each employee's salary difference from the department average excluding that employee?
Compute each partition's salary sum and non-NULL salary count, subtract the current known salary, and divide by the remaining count. Employees with unknown salaries are excluded from both output and the benchmark in this exercise.
WITH totals AS (
SELECT employee_id, department_id, salary,
SUM(salary) OVER (PARTITION BY department_id) AS department_sum,
COUNT(*) OVER (PARTITION BY department_id) AS department_count
FROM employees WHERE salary IS NOT NULL
)
SELECT employee_id, department_id, salary,
1.0 * (department_sum - salary) / NULLIF(department_count - 1, 0) AS peer_average,
salary - 1.0 * (department_sum - salary) / NULLIF(department_count - 1, 0) AS difference
FROM totals
ORDER BY department_id,
WITH totals AS (
SELECT employee_id, department_id, salary,
SUM(salary) OVER (PARTITION BY department_id) AS department_sum,
COUNT(*) OVER (PARTITION BY department_id) AS department_count
FROM employees WHERE salary IS NOT NULL
)
SELECT employee_id, department_id, salary,
1.0 * (department_sum - salary) / NULLIF(department_count - 1, 0) AS peer_average,
salary - 1.0 * (department_sum - salary) / NULLIF(department_count - 1, 0) AS difference
FROM totals
ORDER BY department_id,
WITH totals AS (
SELECT employee_id, department_id, salary,
SUM(salary) OVER (PARTITION BY department_id) AS department_sum,
COUNT(*) OVER (PARTITION BY department_id) AS department_count
FROM employees WHERE salary IS NOT NULL
)
SELECT employee_id, department_id, salary,
1.0 * (department_sum - salary) / NULLIF(department_count - 1, 0) AS peer_average,
salary - 1.0 * (department_sum - salary) / NULLIF(department_count - 1, 0) AS difference
FROM totals
ORDER BY department_id,
Each row compares an employee with other known-salary employees in the same department partition. A sole known-salary employee has no peers, so both derived values are NULL rather than dividing by zero. NULL department IDs form their own unassigned partition; exclude them if that does not represent a valid peer group. This formulation differs from subtracting the department average, which includes the current employee and attenuates the comparison. The decimal multiplier avoids integer division in portable integer fixtures.
65. How do you count unique purchasing customers by product category without double-counting repeat orders?
Join paid orders to item lines and products, then count distinct customer IDs per category. The distinct operation applies to customers within each category, not to amounts or order IDs.
SELECT p.category, COUNT(DISTINCT o.customer_id) AS purchasing_customers
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
JOIN products AS p ON p.product_id = i.product_id
WHERE o.status = 'paid'
GROUP BY p.category
ORDER BY
SELECT p.category, COUNT(DISTINCT o.customer_id) AS purchasing_customers
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
JOIN products AS p ON p.product_id = i.product_id
WHERE o.status = 'paid'
GROUP BY p.category
ORDER BY
SELECT p.category, COUNT(DISTINCT o.customer_id) AS purchasing_customers
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
JOIN products AS p ON p.product_id = i.product_id
WHERE o.status = 'paid'
GROUP BY p.category
ORDER BY
A customer who buys three products in one category across multiple orders contributes once to that category. The same customer may legitimately count in several categories, so summing category counts does not yield a global unique-customer count. Products with NULL categories form one unknown-category group. Categories without purchases are absent; to show them, begin with a distinct category dimension and use a NULL-safe join when joining back to the aggregate. The product table's current category determines classification here. Historical category reporting needs versioned product attributes or a category captured on the order line rather than assuming the current label never changed.
66. How do you find the most frequently purchased product in each category, including ties?
Aggregate paid quantities at product grain, rank those sums within categories, and retain the highest rank. “Most frequently purchased” is defined here as units sold, not number of orders or customers.
WITH units AS (
SELECT p.product_id, p.category, SUM(i.quantity) AS units_sold
FROM products AS p
JOIN order_items AS i ON i.product_id = p.product_id
JOIN orders AS o ON o.order_id = i.order_id
WHERE o.status = 'paid'
GROUP BY p.product_id, p.category
), ranked AS (
SELECT units.*,
DENSE_RANK() OVER (
PARTITION BY category ORDER BY units_sold DESC
) AS rnk
FROM units
)
SELECT category, product_id, units_sold
FROM ranked WHERE rnk = 1
ORDER BY category,
WITH units AS (
SELECT p.product_id, p.category, SUM(i.quantity) AS units_sold
FROM products AS p
JOIN order_items AS i ON i.product_id = p.product_id
JOIN orders AS o ON o.order_id = i.order_id
WHERE o.status = 'paid'
GROUP BY p.product_id, p.category
), ranked AS (
SELECT units.*,
DENSE_RANK() OVER (
PARTITION BY category ORDER BY units_sold DESC
) AS rnk
FROM units
)
SELECT category, product_id, units_sold
FROM ranked WHERE rnk = 1
ORDER BY category,
WITH units AS (
SELECT p.product_id, p.category, SUM(i.quantity) AS units_sold
FROM products AS p
JOIN order_items AS i ON i.product_id = p.product_id
JOIN orders AS o ON o.order_id = i.order_id
WHERE o.status = 'paid'
GROUP BY p.product_id, p.category
), ranked AS (
SELECT units.*,
DENSE_RANK() OVER (
PARTITION BY category ORDER BY units_sold DESC
) AS rnk
FROM units
)
SELECT category, product_id, units_sold
FROM ranked WHERE rnk = 1
ORDER BY category,
The output has one row per winning product, possibly several per category. Unsold products and categories without sales are omitted, rather than declared tied zero-unit winners. Multiple line entries for a product contribute all units because quantity, not line count, is the metric. The schema's positive quantities exclude returns; a returns model would require an explicit net-versus-gross policy. Product ID belongs in the final display ordering, not the rank ordering, because adding it to the rank would break intended ties.
67. How do you summarize order totals and paid amounts together without multiplying either measure?
Pre-aggregate payments by order, then join the resulting one-row-per-order relation to orders. This prevents several payment records from repeating an order's total in a customer-level sum.
WITH payment_totals AS (
SELECT order_id, SUM(amount) AS paid_amount
FROM payments GROUP BY order_id
)
SELECT o.customer_id, SUM(o.total) AS ordered_amount,
SUM(COALESCE(p.paid_amount, 0)) AS received_amount
FROM orders AS o
LEFT JOIN payment_totals AS p ON p.order_id = o.order_id
GROUP BY o.customer_id
ORDER BY
WITH payment_totals AS (
SELECT order_id, SUM(amount) AS paid_amount
FROM payments GROUP BY order_id
)
SELECT o.customer_id, SUM(o.total) AS ordered_amount,
SUM(COALESCE(p.paid_amount, 0)) AS received_amount
FROM orders AS o
LEFT JOIN payment_totals AS p ON p.order_id = o.order_id
GROUP BY o.customer_id
ORDER BY
WITH payment_totals AS (
SELECT order_id, SUM(amount) AS paid_amount
FROM payments GROUP BY order_id
)
SELECT o.customer_id, SUM(o.total) AS ordered_amount,
SUM(COALESCE(p.paid_amount, 0)) AS received_amount
FROM orders AS o
LEFT JOIN payment_totals AS p ON p.order_id = o.order_id
GROUP BY o.customer_id
ORDER BY
This exercise intentionally includes all order statuses and reports ordered value alongside recorded cash receipts, not the article's default paid-order revenue measure. Customers with no orders are absent; an order with no payments contributes its total and zero received amount. Payment amounts are assumed signed according to the payment ledger's stated policy, so a refund can reduce the sum if represented negatively. SUM(DISTINCT o.total) is not a repair for join multiplication: two different orders can legitimately have equal totals and must both count. Additional item-level measures must likewise be aggregated to order grain before joining.
68. How do you return nonempty departments where every employee has a known salary above a threshold?
Use a grouped condition that counts employees and explicitly rejects either a low salary or an unknown salary. Here “every employee exceeds 50,000” treats missing compensation as failure to establish the condition.
SELECT d.department_id
FROM departments AS d
JOIN employees AS e ON e.department_id = d.department_id
GROUP BY d.department_id
HAVING COUNT(*) > 0
AND SUM(CASE WHEN e.salary IS NULL OR e.salary <= 50000
THEN 1 ELSE 0 END) = 0
ORDER BY
SELECT d.department_id
FROM departments AS d
JOIN employees AS e ON e.department_id = d.department_id
GROUP BY d.department_id
HAVING COUNT(*) > 0
AND SUM(CASE WHEN e.salary IS NULL OR e.salary <= 50000
THEN 1 ELSE 0 END) = 0
ORDER BY
SELECT d.department_id
FROM departments AS d
JOIN employees AS e ON e.department_id = d.department_id
GROUP BY d.department_id
HAVING COUNT(*) > 0
AND SUM(CASE WHEN e.salary IS NULL OR e.salary <= 50000
THEN 1 ELSE 0 END) = 0
ORDER BY
The inner join already excludes empty departments; the count makes the nonempty requirement visible. Each result row represents one department whose assigned employees all have a known salary above the threshold. Employees with NULL department IDs do not belong to any department record. Testing only MIN(salary) > 50000 would ignore NULL salaries and could incorrectly approve a department with unknown compensation. If the actual rule is “every employee with a known salary exceeds the threshold,” remove the NULL rejection and separately decide whether a department with no known salaries should qualify. Universal conditions need explicit empty-set and unknown-value policies.
69. How do you select products bought by both of two customers but not by a third?
Build a customer-product set from paid purchases, then apply intersection and difference. PostgreSQL supports INTERSECT and EXCEPT, both of which eliminate duplicates by default.
WITH bought AS (
SELECT o.customer_id, i.product_id
FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid'
), common AS (
SELECT product_id FROM bought WHERE customer_id = 1
INTERSECT
SELECT product_id FROM bought WHERE customer_id = 2
)
SELECT product_id FROM common
EXCEPT
SELECT product_id FROM bought WHERE customer_id = 3
ORDER BY
WITH bought AS (
SELECT o.customer_id, i.product_id
FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid'
), common AS (
SELECT product_id FROM bought WHERE customer_id = 1
INTERSECT
SELECT product_id FROM bought WHERE customer_id = 2
)
SELECT product_id FROM common
EXCEPT
SELECT product_id FROM bought WHERE customer_id = 3
ORDER BY
WITH bought AS (
SELECT o.customer_id, i.product_id
FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid'
), common AS (
SELECT product_id FROM bought WHERE customer_id = 1
INTERSECT
SELECT product_id FROM bought WHERE customer_id = 2
)
SELECT product_id FROM common
EXCEPT
SELECT product_id FROM bought WHERE customer_id = 3
ORDER BY
The output contains unique product IDs purchased by customers 1 and 2 and never purchased by customer 3 in the available paid-order history. Repeated purchases and multiple lines do not change membership. A missing customer behaves like an empty purchase set: if customer 1 or 2 has none, the result is empty; if customer 3 has none, nothing is subtracted. The CTE also removes ambiguity about set-operator grouping. This is a membership question, not a comparison of quantities, and using EXCEPT ALL would introduce multiplicity semantics that are inappropriate for the stated requirement.
70. How do you compare every pair of products co-purchased in the same paid order?
Deduplicate product membership within each order, self-join that set with an ordered ID condition, and count distinct order-level pair occurrences. The strict inequality removes self-pairs and mirrored duplicates.
WITH order_products AS (
SELECT DISTINCT i.order_id, i.product_id
FROM order_items AS i JOIN orders AS o ON o.order_id = i.order_id
WHERE o.status = 'paid'
)
SELECT a.product_id AS product_a, b.product_id AS product_b,
COUNT(*) AS shared_orders
FROM order_products AS a
JOIN order_products AS b
ON b.order_id = a.order_id AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id
ORDER BY shared_orders DESC, product_a,
WITH order_products AS (
SELECT DISTINCT i.order_id, i.product_id
FROM order_items AS i JOIN orders AS o ON o.order_id = i.order_id
WHERE o.status = 'paid'
)
SELECT a.product_id AS product_a, b.product_id AS product_b,
COUNT(*) AS shared_orders
FROM order_products AS a
JOIN order_products AS b
ON b.order_id = a.order_id AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id
ORDER BY shared_orders DESC, product_a,
WITH order_products AS (
SELECT DISTINCT i.order_id, i.product_id
FROM order_items AS i JOIN orders AS o ON o.order_id = i.order_id
WHERE o.status = 'paid'
)
SELECT a.product_id AS product_a, b.product_id AS product_b,
COUNT(*) AS shared_orders
FROM order_products AS a
JOIN order_products AS b
ON b.order_id = a.order_id AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id
ORDER BY shared_orders DESC, product_a,
The output grain is one unordered product pair. An order with product A on three lines still contributes only once to pair A–B, because membership was deduplicated before the join. Quantity does not weight pair counts, and an order with a single distinct product produces no pair. A large basket creates a quadratic number of pairs, so practical workloads may need basket-size limits or a narrower reporting window. Shared orders do not demonstrate causal affinity; popular products can co-occur frequently merely because each appears in many baskets.
71. How do you calculate the 90th-percentile known salary in each department?
Use PostgreSQL's ordered-set aggregate percentile_cont(0.9) to calculate an interpolated 90th-percentile threshold. Cast to double precision deliberately because this overload produces a floating-point analytical value rather than an exact monetary calculation.
SELECT department_id,
percentile_cont(0.9) WITHIN GROUP (
ORDER BY salary::double precision
) AS salary_p90
FROM employees
WHERE salary IS NOT NULL
GROUP BY department_id
ORDER BY
SELECT department_id,
percentile_cont(0.9) WITHIN GROUP (
ORDER BY salary::double precision
) AS salary_p90
FROM employees
WHERE salary IS NOT NULL
GROUP BY department_id
ORDER BY
SELECT department_id,
percentile_cont(0.9) WITHIN GROUP (
ORDER BY salary::double precision
) AS salary_p90
FROM employees
WHERE salary IS NOT NULL
GROUP BY department_id
ORDER BY
Each result row represents a department partition with at least one known salary. For two salaries 40,000 and 60,000, continuous interpolation yields 58,000, even though no employee earns that amount. A single known salary produces that salary. percentile_disc(0.9) instead selects an observed value, so the desired percentile convention must be established before comparing results across systems. Unknown salaries are omitted, and departments containing only unknown salaries disappear after filtering. This threshold is not a guarantee that exactly ten percent of employees earn more: ties and small populations prevent that interpretation. Use unrounded values for analytical comparisons and round only for presentation.
72. How do you calculate an exact-numeric median without a percentile aggregate?
Rank known salaries, count the population, and average the middle one or two rows. Integer division in the position formulas intentionally chooses the correct central positions for odd and even counts.
WITH ranked AS (
SELECT department_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department_id ORDER BY salary, employee_id
) AS rn,
COUNT(*) OVER (PARTITION BY department_id) AS n
FROM employees WHERE salary IS NOT NULL
)
SELECT department_id, AVG(salary) AS median_salary
FROM ranked
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY department_id
ORDER BY
WITH ranked AS (
SELECT department_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department_id ORDER BY salary, employee_id
) AS rn,
COUNT(*) OVER (PARTITION BY department_id) AS n
FROM employees WHERE salary IS NOT NULL
)
SELECT department_id, AVG(salary) AS median_salary
FROM ranked
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY department_id
ORDER BY
WITH ranked AS (
SELECT department_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department_id ORDER BY salary, employee_id
) AS rn,
COUNT(*) OVER (PARTITION BY department_id) AS n
FROM employees WHERE salary IS NOT NULL
)
SELECT department_id, AVG(salary) AS median_salary
FROM ranked
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY department_id
ORDER BY
In PostgreSQL, n is an integer count, so division by integer 2 truncates: five rows select position 3, while four rows select positions 2 and 3. AVG(numeric) preserves numeric arithmetic, unlike a double-precision percentile. Employee ID stabilizes position assignments for equal salaries but cannot change the median because tied values are equal. Unknown salaries are omitted, and empty known-salary populations produce no row. This method transfers to engines with compatible integer-division semantics; engines that perform fractional division require explicit floor or integer casts rather than blindly copying the formulas.
73. How do you find each customer's first paid order that brings cumulative revenue to at least 100?
Calculate a deterministic cumulative total, then rank only the rows that meet the threshold. Retain the first qualifying row rather than every subsequent order above the threshold.
WITH running AS (
SELECT order_id, customer_id, ordered_at,
SUM(total) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue
FROM orders WHERE status = 'paid'
), crossed AS (
SELECT running.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS rn
FROM running WHERE cumulative_revenue >= 100
)
SELECT customer_id, order_id, cumulative_revenue
FROM crossed WHERE rn = 1
ORDER BY
WITH running AS (
SELECT order_id, customer_id, ordered_at,
SUM(total) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue
FROM orders WHERE status = 'paid'
), crossed AS (
SELECT running.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS rn
FROM running WHERE cumulative_revenue >= 100
)
SELECT customer_id, order_id, cumulative_revenue
FROM crossed WHERE rn = 1
ORDER BY
WITH running AS (
SELECT order_id, customer_id, ordered_at,
SUM(total) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue
FROM orders WHERE status = 'paid'
), crossed AS (
SELECT running.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS rn
FROM running WHERE cumulative_revenue >= 100
)
SELECT customer_id, order_id, cumulative_revenue
FROM crossed WHERE rn = 1
ORDER BY
The output has at most one row per customer; customers who never reach 100 are absent. Nonnegative order totals make the cumulative measure monotonic under the shared contract. If returns or negative adjustments were introduced, this query would still identify the first observed qualifying order, but “crossing” and later recrossings would require a more specific rule. Equal timestamps use order ID as an agreed sequence. Filtering the reporting period before accumulation would change historical threshold attainment and should not be done unless that is the intended metric.
74. How do you find paid orders immediately preceded by two canceled orders for the same customer?
Compute the two preceding statuses over all orders, then filter for the required three-order sequence. Do not filter to paid orders before the window: doing so would remove the canceled attempts that the exercise needs to inspect.
WITH sequence AS (
SELECT order_id, customer_id, ordered_at, status,
LAG(status, 1) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS previous_status,
LAG(status, 2) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS second_previous_status
FROM orders
)
SELECT customer_id, order_id, ordered_at
FROM sequence
WHERE status = 'paid'
AND previous_status = 'canceled'
AND second_previous_status = 'canceled'
ORDER BY customer_id, ordered_at,
WITH sequence AS (
SELECT order_id, customer_id, ordered_at, status,
LAG(status, 1) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS previous_status,
LAG(status, 2) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS second_previous_status
FROM orders
)
SELECT customer_id, order_id, ordered_at
FROM sequence
WHERE status = 'paid'
AND previous_status = 'canceled'
AND second_previous_status = 'canceled'
ORDER BY customer_id, ordered_at,
WITH sequence AS (
SELECT order_id, customer_id, ordered_at, status,
LAG(status, 1) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS previous_status,
LAG(status, 2) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS second_previous_status
FROM orders
)
SELECT customer_id, order_id, ordered_at
FROM sequence
WHERE status = 'paid'
AND previous_status = 'canceled'
AND second_previous_status = 'canceled'
ORDER BY customer_id, ordered_at,
Each result is a paid order, not a customer summary; a customer can have several qualifying sequences. A pending order between either canceled attempt and the paid order breaks the sequence. Fewer than two preceding orders cannot qualify because the missing LAG result is NULL. The ID tie-breaker defines a stable convention for simultaneous orders rather than proving their physical chronology. This tests immediate adjacency, not merely whether two canceled orders exist anywhere in the customer's history. Three or more consecutive cancellations followed by a paid order still qualify because its last two predecessors are canceled.
75. How do you measure elapsed hours between a customer's consecutive paid orders?
Use LAG to retrieve the preceding paid-order instant, subtract the two timestamptz values, and convert the resulting interval to hours. PostgreSQL's epoch extraction measures elapsed time rather than local calendar-date boundaries.
WITH sequence AS (
SELECT order_id, customer_id, ordered_at,
LAG(ordered_at) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS previous_at
FROM orders WHERE status = 'paid'
)
SELECT order_id, customer_id,
EXTRACT(EPOCH FROM (ordered_at - previous_at)) / 3600.0 AS elapsed_hours
FROM sequence
ORDER BY customer_id, ordered_at,
WITH sequence AS (
SELECT order_id, customer_id, ordered_at,
LAG(ordered_at) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS previous_at
FROM orders WHERE status = 'paid'
)
SELECT order_id, customer_id,
EXTRACT(EPOCH FROM (ordered_at - previous_at)) / 3600.0 AS elapsed_hours
FROM sequence
ORDER BY customer_id, ordered_at,
WITH sequence AS (
SELECT order_id, customer_id, ordered_at,
LAG(ordered_at) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS previous_at
FROM orders WHERE status = 'paid'
)
SELECT order_id, customer_id,
EXTRACT(EPOCH FROM (ordered_at - previous_at)) / 3600.0 AS elapsed_hours
FROM sequence
ORDER BY customer_id, ordered_at,
The first paid order has NULL elapsed hours because no prior instant exists. Equal timestamps yield zero even though the ID tie-breaker determines their sequence. This is not the number of local dates crossed: a purchase just before midnight followed shortly afterward may have a tiny elapsed gap but a one-day calendar difference. Zoned timestamps refer to actual instants, so daylight-saving transitions do not make this subtraction a fixed count of nominal local hours. PostgreSQL-specific interval and epoch syntax should be adapted explicitly for other engines rather than replaced with string arithmetic.
76. How do you find event rows where a customer's event type changed from the previous row?
Calculate the predecessor ID and predecessor event type, then compare only rows that have an actual predecessor. The event ID provides both a deterministic tie-breaker and an unambiguous first-row indicator.
WITH previous AS (
SELECT event_id, customer_id, occurred_at, event_type,
LAG(event_id) OVER (
PARTITION BY customer_id ORDER BY occurred_at, event_id
) AS previous_id,
LAG(event_type) OVER (
PARTITION BY customer_id ORDER BY occurred_at, event_id
) AS previous_type
FROM events
)
SELECT event_id, customer_id, previous_type, event_type
FROM previous
WHERE previous_id IS NOT NULL AND event_type <> previous_type
ORDER BY customer_id, occurred_at,
WITH previous AS (
SELECT event_id, customer_id, occurred_at, event_type,
LAG(event_id) OVER (
PARTITION BY customer_id ORDER BY occurred_at, event_id
) AS previous_id,
LAG(event_type) OVER (
PARTITION BY customer_id ORDER BY occurred_at, event_id
) AS previous_type
FROM events
)
SELECT event_id, customer_id, previous_type, event_type
FROM previous
WHERE previous_id IS NOT NULL AND event_type <> previous_type
ORDER BY customer_id, occurred_at,
WITH previous AS (
SELECT event_id, customer_id, occurred_at, event_type,
LAG(event_id) OVER (
PARTITION BY customer_id ORDER BY occurred_at, event_id
) AS previous_id,
LAG(event_type) OVER (
PARTITION BY customer_id ORDER BY occurred_at, event_id
) AS previous_type
FROM events
)
SELECT event_id, customer_id, previous_type, event_type
FROM previous
WHERE previous_id IS NOT NULL AND event_type <> previous_type
ORDER BY customer_id, occurred_at,
The output represents changed rows, not complete runs or sessions. The shared schema makes event_type non-NULL, so ordinary inequality is sufficient; nullable state values would require an explicit NULL-safe change policy such as PostgreSQL's IS DISTINCT FROM. The first event is excluded even if its type differs from a fabricated default. Equal timestamps are ordered by ID for repeatability, but that ordering is a modeling decision rather than evidence about simultaneous real-world actions. Filtering to a subset of types before computing LAG would change which event is considered previous.
77. How do you turn fixed monthly revenue columns into one row per month?
Use UNION ALL to unpivot a known set of columns. An inline fixture makes the wide-table contract explicit and demonstrates the chosen NULL policy.
WITH monthly(customer_id, jan_revenue, feb_revenue) AS (
VALUES (1, 100, 0), (2, 50, NULL)
)
SELECT customer_id, '2026-01' AS month, jan_revenue AS revenue
FROM monthly
UNION ALL
SELECT customer_id, '2026-02' AS month, feb_revenue AS revenue
FROM monthly
ORDER BY customer_id, month
WITH monthly(customer_id, jan_revenue, feb_revenue) AS (
VALUES (1, 100, 0), (2, 50, NULL)
)
SELECT customer_id, '2026-01' AS month, jan_revenue AS revenue
FROM monthly
UNION ALL
SELECT customer_id, '2026-02' AS month, feb_revenue AS revenue
FROM monthly
ORDER BY customer_id, month
WITH monthly(customer_id, jan_revenue, feb_revenue) AS (
VALUES (1, 100, 0), (2, 50, NULL)
)
SELECT customer_id, '2026-01' AS month, jan_revenue AS revenue
FROM monthly
UNION ALL
SELECT customer_id, '2026-02' AS month, feb_revenue AS revenue
FROM monthly
ORDER BY customer_id, month
The result contains four customer-month rows, including customer 2's unknown February revenue as NULL and customer 1's known zero as zero. UNION ALL preserves each source record instead of paying for duplicate elimination or accidentally hiding duplicate input rows. The exercise assumes one wide row per customer; if that key is not unique, output duplicates reflect the source and require diagnosis rather than a cosmetic DISTINCT. This static solution is suitable for fixed columns. An extensible reporting model usually stores month and revenue as rows initially, avoiding schema and query changes whenever another month is added.
78. How do you return country paid-revenue subtotals and a grand total while distinguishing NULL countries?
Use PostgreSQL GROUPING SETS and expose GROUPING(country) as a level indicator. The indicator distinguishes the grand-total placeholder NULL from a genuine unknown-country group.
SELECT c.country,
GROUPING(c.country) AS is_grand_total,
SUM(o.total) AS paid_revenue
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
GROUP BY GROUPING SETS ((c.country), ())
ORDER BY is_grand_total,
SELECT c.country,
GROUPING(c.country) AS is_grand_total,
SUM(o.total) AS paid_revenue
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
GROUP BY GROUPING SETS ((c.country), ())
ORDER BY is_grand_total,
SELECT c.country,
GROUPING(c.country) AS is_grand_total,
SUM(o.total) AS paid_revenue
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
GROUP BY GROUPING SETS ((c.country), ())
ORDER BY is_grand_total,
The output contains one subtotal for each observed country, including an actual NULL country if present, followed by a grand total. A genuine unknown-country row has indicator 0; the all-country row has indicator 1. Replacing every NULL country with the label “Total” would incorrectly merge two meanings in the presentation. With no qualifying orders, the grand-total grouping still yields a row whose sum is NULL; use a deliberate zero policy if the report demands it. Each paid order joins one customer under the schema contract, avoiding measure multiplication. This example demonstrates grouping syntax, not a promise that all SQL engines implement it identically.
79. How do you report the distribution of customers by their number of paid purchases, including nonbuyers?
Count paid orders for every customer first, then count customers at each purchase-count value. This two-stage aggregation keeps customers with zero paid orders in the distribution without confusing orders with people.
WITH customer_counts AS (
SELECT c.customer_id, COUNT(o.order_id) AS paid_purchases
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id
)
SELECT paid_purchases, COUNT(*) AS customers
FROM customer_counts
GROUP BY paid_purchases
ORDER BY
WITH customer_counts AS (
SELECT c.customer_id, COUNT(o.order_id) AS paid_purchases
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id
)
SELECT paid_purchases, COUNT(*) AS customers
FROM customer_counts
GROUP BY paid_purchases
ORDER BY
WITH customer_counts AS (
SELECT c.customer_id, COUNT(o.order_id) AS paid_purchases
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id
)
SELECT paid_purchases, COUNT(*) AS customers
FROM customer_counts
GROUP BY paid_purchases
ORDER BY
The output grain is a purchase-frequency value, such as zero purchases or three purchases, not an individual customer. Each customer belongs to exactly one frequency group, so the output customer counts sum to the size of the customer table. Status filtering belongs in the join condition: a WHERE o.status = 'paid' condition would drop nonbuyers and customers whose orders are all unpaid. The result includes only observed frequency values; a missing value such as one purchase is not automatically emitted with count zero. If every possible frequency in a bounded range is required, join the aggregate to a separately defined integer domain.
80. How do you return only departments whose total known payroll exceeds the average department payroll?
Aggregate payroll to department grain, retain empty departments as zero, and compare those totals with the average over that same department population. This avoids weighting larger departments more heavily merely because they have more employee rows.
WITH payroll AS (
SELECT d.department_id, COALESCE(SUM(e.salary), 0) AS known_payroll
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.department_id
GROUP BY d.department_id
)
SELECT department_id, known_payroll
FROM payroll
WHERE known_payroll > (SELECT AVG(known_payroll) FROM payroll)
ORDER BY
WITH payroll AS (
SELECT d.department_id, COALESCE(SUM(e.salary), 0) AS known_payroll
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.department_id
GROUP BY d.department_id
)
SELECT department_id, known_payroll
FROM payroll
WHERE known_payroll > (SELECT AVG(known_payroll) FROM payroll)
ORDER BY
WITH payroll AS (
SELECT d.department_id, COALESCE(SUM(e.salary), 0) AS known_payroll
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.department_id
GROUP BY d.department_id
)
SELECT department_id, known_payroll
FROM payroll
WHERE known_payroll > (SELECT AVG(known_payroll) FROM payroll)
ORDER BY
The result contains each qualifying department once. The benchmark includes every department record, including empty departments and departments with only unknown salaries, both represented as zero known payroll. That is an explicit “sum of known compensation” policy, not an assertion that unknown salaries are truly zero. If missing salaries should invalidate comparison, identify and exclude affected departments before computing both sides. Unassigned employees with NULL department IDs are outside this population. If no departments exist, the comparison baseline is NULL and the result is empty. The same-grain CTE makes the benchmark's weighting and population inspectable during an interview.
SQL Coding Interview Questions and Answers for Experienced
Senior candidates should defend invariants and failure behavior as well as write SQL. PostgreSQL-specific features are deliberate here, and any mutation example belongs only in an isolated disposable fixture until its safeguards have been reviewed.
81. How would you list every employee below a specified manager without looping on corrupt hierarchy data?
Start at manager 1 and expand direct reports, carrying the visited employee IDs. This PostgreSQL solution uses arrays and ANY; the output is one row per reachable employee in the single-manager schema, with the starting manager excluded.
WITH RECURSIVE tree AS (
SELECT employee_id, manager_id, 0 AS depth,
ARRAY[employee_id] AS path
FROM employees WHERE employee_id = 1
UNION ALL
SELECT e.employee_id, e.manager_id, t.depth + 1,
t.path || e.employee_id
FROM tree t JOIN employees e ON e.manager_id = t.employee_id
WHERE NOT e.employee_id = ANY(t.path)
)
SELECT employee_id, depth FROM tree
WHERE depth > 0 ORDER BY depth,
WITH RECURSIVE tree AS (
SELECT employee_id, manager_id, 0 AS depth,
ARRAY[employee_id] AS path
FROM employees WHERE employee_id = 1
UNION ALL
SELECT e.employee_id, e.manager_id, t.depth + 1,
t.path || e.employee_id
FROM tree t JOIN employees e ON e.manager_id = t.employee_id
WHERE NOT e.employee_id = ANY(t.path)
)
SELECT employee_id, depth FROM tree
WHERE depth > 0 ORDER BY depth,
WITH RECURSIVE tree AS (
SELECT employee_id, manager_id, 0 AS depth,
ARRAY[employee_id] AS path
FROM employees WHERE employee_id = 1
UNION ALL
SELECT e.employee_id, e.manager_id, t.depth + 1,
t.path || e.employee_id
FROM tree t JOIN employees e ON e.manager_id = t.employee_id
WHERE NOT e.employee_id = ANY(t.path)
)
SELECT employee_id, depth FROM tree
WHERE depth > 0 ORDER BY depth,
The path guard prevents revisiting an ancestor; UNION alone would not do so when depth changes. A missing starting manager returns no rows. The query deliberately does not report disconnected cycles or repair bad data. A deep or very wide hierarchy can still be expensive, so validate constraints and inspect production limits separately rather than treating cycle protection as a performance guarantee.
82. How would you calculate the salary budget for every manager's complete subtree?
Build an ancestor-descendant relation that includes each employee as their own descendant, then aggregate salaries by ancestor. The PostgreSQL array path prevents cycles and makes the inclusion rule explicit.
WITH RECURSIVE closure AS (
SELECT employee_id AS ancestor, employee_id AS descendant,
ARRAY[employee_id] AS path
FROM employees
UNION ALL
SELECT c.ancestor, e.employee_id, c.path || e.employee_id
FROM closure c JOIN employees e ON e.manager_id = c.descendant
WHERE NOT e.employee_id = ANY(c.path)
)
SELECT c.ancestor AS employee_id,
SUM(e.salary) AS known_salary_budget,
COUNT(*) - COUNT(e.salary) AS unknown_salaries
FROM closure c JOIN employees e ON e.employee_id = c.descendant
GROUP BY c.ancestor ORDER BY
WITH RECURSIVE closure AS (
SELECT employee_id AS ancestor, employee_id AS descendant,
ARRAY[employee_id] AS path
FROM employees
UNION ALL
SELECT c.ancestor, e.employee_id, c.path || e.employee_id
FROM closure c JOIN employees e ON e.manager_id = c.descendant
WHERE NOT e.employee_id = ANY(c.path)
)
SELECT c.ancestor AS employee_id,
SUM(e.salary) AS known_salary_budget,
COUNT(*) - COUNT(e.salary) AS unknown_salaries
FROM closure c JOIN employees e ON e.employee_id = c.descendant
GROUP BY c.ancestor ORDER BY
WITH RECURSIVE closure AS (
SELECT employee_id AS ancestor, employee_id AS descendant,
ARRAY[employee_id] AS path
FROM employees
UNION ALL
SELECT c.ancestor, e.employee_id, c.path || e.employee_id
FROM closure c JOIN employees e ON e.manager_id = c.descendant
WHERE NOT e.employee_id = ANY(c.path)
)
SELECT c.ancestor AS employee_id,
SUM(e.salary) AS known_salary_budget,
COUNT(*) - COUNT(e.salary) AS unknown_salaries
FROM closure c JOIN employees e ON e.employee_id = c.descendant
GROUP BY c.ancestor ORDER BY
The result has one row per employee, including leaf employees and their own salary. Unknown salaries are counted rather than silently called zero; an all-NULL subtree has a NULL sum. With one manager per employee there is one simple path to each descendant. A multiple-parent graph would need a distinct ancestor-descendant relation before summing, otherwise shared descendants could be charged more than once.
83. How would you find the shortest hop count between nodes in a small directed graph?
Enumerate simple paths from node 1, preventing node revisits, then take the minimum depth for each destination. This PostgreSQL exercise defines its graph inline so its result is reproducible.
WITH RECURSIVE edges(src, dst) AS (
VALUES (1,2),(1,3),(2,4),(3,4),(4,2)
), walk(node, depth, path) AS (
SELECT 1, 0, ARRAY[1]
UNION ALL
SELECT e.dst, w.depth + 1, w.path || e.dst
FROM walk w JOIN edges e ON e.src = w.node
WHERE NOT e.dst = ANY(w.path)
)
SELECT node, MIN(depth) AS minimum_hops
FROM walk GROUP BY node ORDER BY
WITH RECURSIVE edges(src, dst) AS (
VALUES (1,2),(1,3),(2,4),(3,4),(4,2)
), walk(node, depth, path) AS (
SELECT 1, 0, ARRAY[1]
UNION ALL
SELECT e.dst, w.depth + 1, w.path || e.dst
FROM walk w JOIN edges e ON e.src = w.node
WHERE NOT e.dst = ANY(w.path)
)
SELECT node, MIN(depth) AS minimum_hops
FROM walk GROUP BY node ORDER BY
WITH RECURSIVE edges(src, dst) AS (
VALUES (1,2),(1,3),(2,4),(3,4),(4,2)
), walk(node, depth, path) AS (
SELECT 1, 0, ARRAY[1]
UNION ALL
SELECT e.dst, w.depth + 1, w.path || e.dst
FROM walk w JOIN edges e ON e.src = w.node
WHERE NOT e.dst = ANY(w.path)
)
SELECT node, MIN(depth) AS minimum_hops
FROM walk GROUP BY node ORDER BY
The output is (1,0), (2,1), (3,1), (4,2). All edges have equal cost; this is not a weighted shortest-path algorithm. The cycle cannot improve a shortest path and is safely excluded. Enumeration can grow exponentially even though the graph is finite, making this a correctness-oriented small-graph answer rather than a recommended algorithm for a huge routing graph. Duplicate edges would add redundant work without changing the minimum.
84. How would you divide customer events into sessions using a thirty-minute inactivity threshold?
Use LAG to find the preceding event, mark session boundaries, and cumulatively count those markers. Here a gap strictly greater than thirty minutes starts a new session; exactly thirty minutes does not. PostgreSQL interval syntax expresses elapsed time.
WITH previous AS (
SELECT e.*, LAG(occurred_at) OVER (
PARTITION BY customer_id ORDER BY occurred_at, event_id
) AS previous_at
FROM events e
), boundaries AS (
SELECT *, CASE WHEN previous_at IS NULL
OR occurred_at > previous_at + INTERVAL '30 minutes'
THEN 1 ELSE 0 END AS begins
FROM previous
)
SELECT event_id, customer_id,
SUM(begins) OVER (PARTITION BY customer_id
ORDER BY occurred_at, event_id
ROWS UNBOUNDED PRECEDING) AS session_number
FROM boundaries ORDER BY customer_id, occurred_at,
WITH previous AS (
SELECT e.*, LAG(occurred_at) OVER (
PARTITION BY customer_id ORDER BY occurred_at, event_id
) AS previous_at
FROM events e
), boundaries AS (
SELECT *, CASE WHEN previous_at IS NULL
OR occurred_at > previous_at + INTERVAL '30 minutes'
THEN 1 ELSE 0 END AS begins
FROM previous
)
SELECT event_id, customer_id,
SUM(begins) OVER (PARTITION BY customer_id
ORDER BY occurred_at, event_id
ROWS UNBOUNDED PRECEDING) AS session_number
FROM boundaries ORDER BY customer_id, occurred_at,
WITH previous AS (
SELECT e.*, LAG(occurred_at) OVER (
PARTITION BY customer_id ORDER BY occurred_at, event_id
) AS previous_at
FROM events e
), boundaries AS (
SELECT *, CASE WHEN previous_at IS NULL
OR occurred_at > previous_at + INTERVAL '30 minutes'
THEN 1 ELSE 0 END AS begins
FROM previous
)
SELECT event_id, customer_id,
SUM(begins) OVER (PARTITION BY customer_id
ORDER BY occurred_at, event_id
ROWS UNBOUNDED PRECEDING) AS session_number
FROM boundaries ORDER BY customer_id, occurred_at,
Every input event receives one customer-local session number. Event IDs break timestamp ties deterministically. Do not first restrict events to the reporting day: the first event inside the day may belong to a session that began earlier. Late-arriving events can change historical session numbers, so these numbers are not immutable business identifiers.
85. How would you find a customer's first purchase that follows a signup within twenty-four hours?
Find each customer's first signup, then choose the earliest qualifying purchase event using a lateral subquery. This PostgreSQL solution returns one row for every customer who signed up, including a NULL purchase when none qualifies.
WITH signup AS (
SELECT customer_id, MIN(occurred_at) AS signup_at
FROM events WHERE event_type = 'signup' GROUP BY customer_id
)
SELECT s.customer_id, s.signup_at, p.event_id, p.occurred_at
FROM signup s
LEFT JOIN LATERAL (
SELECT event_id, occurred_at FROM events e
WHERE e.customer_id = s.customer_id
AND e.event_type = 'purchase'
AND e.occurred_at >= s.signup_at
AND e.occurred_at < s.signup_at + INTERVAL '24 hours'
ORDER BY e.occurred_at, e.event_id LIMIT 1
) p ON TRUE
ORDER BY
WITH signup AS (
SELECT customer_id, MIN(occurred_at) AS signup_at
FROM events WHERE event_type = 'signup' GROUP BY customer_id
)
SELECT s.customer_id, s.signup_at, p.event_id, p.occurred_at
FROM signup s
LEFT JOIN LATERAL (
SELECT event_id, occurred_at FROM events e
WHERE e.customer_id = s.customer_id
AND e.event_type = 'purchase'
AND e.occurred_at >= s.signup_at
AND e.occurred_at < s.signup_at + INTERVAL '24 hours'
ORDER BY e.occurred_at, e.event_id LIMIT 1
) p ON TRUE
ORDER BY
WITH signup AS (
SELECT customer_id, MIN(occurred_at) AS signup_at
FROM events WHERE event_type = 'signup' GROUP BY customer_id
)
SELECT s.customer_id, s.signup_at, p.event_id, p.occurred_at
FROM signup s
LEFT JOIN LATERAL (
SELECT event_id, occurred_at FROM events e
WHERE e.customer_id = s.customer_id
AND e.event_type = 'purchase'
AND e.occurred_at >= s.signup_at
AND e.occurred_at < s.signup_at + INTERVAL '24 hours'
ORDER BY e.occurred_at, e.event_id LIMIT 1
) p ON TRUE
ORDER BY
The conversion window is half-open and permits purchase at the signup instant. If the business requires a strictly later event, change that lower boundary or introduce a common event sequence. The smallest event ID wins simultaneous purchase ties. This measures event conversion, not paid-order revenue, and does not multiply customers who emitted duplicate purchase events.
86. How would you count customers completing an ordered view-cart-purchase funnel?
Use nested existence checks to require strictly increasing event times, counting each qualifying customer once. The funnel spans all stored history; a session or maximum-duration constraint would be an additional requirement rather than an implicit assumption.
SELECT COUNT(*) AS converted_customers
FROM customers c
WHERE EXISTS (
SELECT 1 FROM events v
WHERE v.customer_id = c.customer_id AND v.event_type = 'view'
AND EXISTS (
SELECT 1 FROM events a
WHERE a.customer_id = c.customer_id AND a.event_type = 'cart'
AND a.occurred_at > v.occurred_at
AND EXISTS (
SELECT 1 FROM events p
WHERE p.customer_id = c.customer_id
AND p.event_type = 'purchase'
AND p.occurred_at > a.occurred_at
)
)
)
SELECT COUNT(*) AS converted_customers
FROM customers c
WHERE EXISTS (
SELECT 1 FROM events v
WHERE v.customer_id = c.customer_id AND v.event_type = 'view'
AND EXISTS (
SELECT 1 FROM events a
WHERE a.customer_id = c.customer_id AND a.event_type = 'cart'
AND a.occurred_at > v.occurred_at
AND EXISTS (
SELECT 1 FROM events p
WHERE p.customer_id = c.customer_id
AND p.event_type = 'purchase'
AND p.occurred_at > a.occurred_at
)
)
)
SELECT COUNT(*) AS converted_customers
FROM customers c
WHERE EXISTS (
SELECT 1 FROM events v
WHERE v.customer_id = c.customer_id AND v.event_type = 'view'
AND EXISTS (
SELECT 1 FROM events a
WHERE a.customer_id = c.customer_id AND a.event_type = 'cart'
AND a.occurred_at > v.occurred_at
AND EXISTS (
SELECT 1 FROM events p
WHERE p.customer_id = c.customer_id
AND p.event_type = 'purchase'
AND p.occurred_at > a.occurred_at
)
)
)
Repeated views, carts, and purchases cannot inflate this customer-grain count. Simultaneous events do not establish an order under the stated rule. Unlike requiring each customer's first event of each type to be ordered, this admits a later valid sequence after earlier unsuccessful activity. The empty population produces a single row containing zero. Suitable customer/type/time indexes may help, but plans and data distribution determine the actual cost.
87. How would you calculate day-seven retention without counting users whose observation window is incomplete?
Anchor each customer to the UTC date of their first signup, then check activity on exactly the seventh following UTC date. An exclusive data-completeness cutoff of October 27 means all of October 26 is observable.
WITH signup AS (
SELECT customer_id,
MIN((occurred_at AT TIME ZONE 'UTC')::date) AS cohort_day
FROM events WHERE event_type = 'signup' GROUP BY customer_id
), eligible AS (
SELECT * FROM signup WHERE cohort_day + 7 < DATE '2026-10-27'
)
SELECT cohort_day, COUNT(*) AS eligible_customers,
SUM(CASE WHEN EXISTS (
SELECT 1 FROM events e WHERE e.customer_id = s.customer_id
AND e.event_type = 'active'
AND (e.occurred_at AT TIME ZONE 'UTC')::date = s.cohort_day + 7
) THEN 1 ELSE 0 END) AS retained_customers
FROM eligible s GROUP BY cohort_day ORDER BY
WITH signup AS (
SELECT customer_id,
MIN((occurred_at AT TIME ZONE 'UTC')::date) AS cohort_day
FROM events WHERE event_type = 'signup' GROUP BY customer_id
), eligible AS (
SELECT * FROM signup WHERE cohort_day + 7 < DATE '2026-10-27'
)
SELECT cohort_day, COUNT(*) AS eligible_customers,
SUM(CASE WHEN EXISTS (
SELECT 1 FROM events e WHERE e.customer_id = s.customer_id
AND e.event_type = 'active'
AND (e.occurred_at AT TIME ZONE 'UTC')::date = s.cohort_day + 7
) THEN 1 ELSE 0 END) AS retained_customers
FROM eligible s GROUP BY cohort_day ORDER BY
WITH signup AS (
SELECT customer_id,
MIN((occurred_at AT TIME ZONE 'UTC')::date) AS cohort_day
FROM events WHERE event_type = 'signup' GROUP BY customer_id
), eligible AS (
SELECT * FROM signup WHERE cohort_day + 7 < DATE '2026-10-27'
)
SELECT cohort_day, COUNT(*) AS eligible_customers,
SUM(CASE WHEN EXISTS (
SELECT 1 FROM events e WHERE e.customer_id = s.customer_id
AND e.event_type = 'active'
AND (e.occurred_at AT TIME ZONE 'UTC')::date = s.cohort_day + 7
) THEN 1 ELSE 0 END) AS retained_customers
FROM eligible s GROUP BY cohort_day ORDER BY
This PostgreSQL query counts customers rather than events and excludes immature cohorts. Cohorts with no signup customers are absent, while a mature cohort with no returning customers has zero retained customers. The supplied cutoff must reflect ingestion completeness, not merely the wall clock. Calendar-day retention differs from activity exactly 168 hours after signup.
88. How would you calculate monthly cohort retention with zero-filled month offsets?
Create offsets zero through two for every first-paid-order cohort, then left join distinct customer-month activity. PostgreSQL month truncation is performed after converting timestamps to UTC.
WITH activity AS (
SELECT DISTINCT customer_id,
date_trunc('month', ordered_at AT TIME ZONE 'UTC')::date AS month
FROM orders WHERE status = 'paid'
), members AS (
SELECT customer_id, MIN(month) AS cohort FROM activity GROUP BY customer_id
), cohorts AS (
SELECT cohort, COUNT(*) AS cohort_size FROM members GROUP BY cohort
), offsets(n) AS (VALUES (0),(1),(2))
SELECT c.cohort, x.n AS month_offset, c.cohort_size,
COUNT(a.customer_id) AS retained
FROM cohorts c CROSS JOIN offsets x
JOIN members m ON m.cohort = c.cohort
LEFT JOIN activity a ON a.customer_id = m.customer_id
AND a.month = (c.cohort + x.n * INTERVAL '1 month')::date
WHERE c.cohort + (x.n + 1) * INTERVAL '1 month' <= DATE '2026-10-01'
GROUP BY c.cohort, x.n, c.cohort_size ORDER BY c.cohort,
WITH activity AS (
SELECT DISTINCT customer_id,
date_trunc('month', ordered_at AT TIME ZONE 'UTC')::date AS month
FROM orders WHERE status = 'paid'
), members AS (
SELECT customer_id, MIN(month) AS cohort FROM activity GROUP BY customer_id
), cohorts AS (
SELECT cohort, COUNT(*) AS cohort_size FROM members GROUP BY cohort
), offsets(n) AS (VALUES (0),(1),(2))
SELECT c.cohort, x.n AS month_offset, c.cohort_size,
COUNT(a.customer_id) AS retained
FROM cohorts c CROSS JOIN offsets x
JOIN members m ON m.cohort = c.cohort
LEFT JOIN activity a ON a.customer_id = m.customer_id
AND a.month = (c.cohort + x.n * INTERVAL '1 month')::date
WHERE c.cohort + (x.n + 1) * INTERVAL '1 month' <= DATE '2026-10-01'
GROUP BY c.cohort, x.n, c.cohort_size ORDER BY c.cohort,
WITH activity AS (
SELECT DISTINCT customer_id,
date_trunc('month', ordered_at AT TIME ZONE 'UTC')::date AS month
FROM orders WHERE status = 'paid'
), members AS (
SELECT customer_id, MIN(month) AS cohort FROM activity GROUP BY customer_id
), cohorts AS (
SELECT cohort, COUNT(*) AS cohort_size FROM members GROUP BY cohort
), offsets(n) AS (VALUES (0),(1),(2))
SELECT c.cohort, x.n AS month_offset, c.cohort_size,
COUNT(a.customer_id) AS retained
FROM cohorts c CROSS JOIN offsets x
JOIN members m ON m.cohort = c.cohort
LEFT JOIN activity a ON a.customer_id = m.customer_id
AND a.month = (c.cohort + x.n * INTERVAL '1 month')::date
WHERE c.cohort + (x.n + 1) * INTERVAL '1 month' <= DATE '2026-10-01'
GROUP BY c.cohort, x.n, c.cohort_size ORDER BY c.cohort,
The cutoff declares data complete through September. Only fully observable months appear, distinguishing zero retention from unobserved future periods. Deduplicating activity prevents multiple orders from inflating retention. Month zero includes every cohort member by definition. Cohorts are based on the available history, so incomplete historical imports can misclassify long-standing customers as new.
89. How would you compute a seven-day rolling distinct active-customer count?
Generate the report dates, then count distinct customers whose activity falls within each date's seven-day UTC window. This PostgreSQL query returns a zero for days without activity rather than dropping those dates.
WITH days AS (
SELECT d::date AS day
FROM generate_series(TIMESTAMP '2026-10-01',
TIMESTAMP '2026-10-07', INTERVAL '1 day') AS g(d)
)
SELECT d.day, COUNT(DISTINCT e.customer_id) AS active_customers
FROM days d LEFT JOIN events e
ON e.event_type = 'active'
AND e.occurred_at >= ((d.day - 6)::timestamp AT TIME ZONE 'UTC')
AND e.occurred_at < ((d.day + 1)::timestamp AT TIME ZONE 'UTC')
GROUP BY d.day ORDER BY
WITH days AS (
SELECT d::date AS day
FROM generate_series(TIMESTAMP '2026-10-01',
TIMESTAMP '2026-10-07', INTERVAL '1 day') AS g(d)
)
SELECT d.day, COUNT(DISTINCT e.customer_id) AS active_customers
FROM days d LEFT JOIN events e
ON e.event_type = 'active'
AND e.occurred_at >= ((d.day - 6)::timestamp AT TIME ZONE 'UTC')
AND e.occurred_at < ((d.day + 1)::timestamp AT TIME ZONE 'UTC')
GROUP BY d.day ORDER BY
WITH days AS (
SELECT d::date AS day
FROM generate_series(TIMESTAMP '2026-10-01',
TIMESTAMP '2026-10-07', INTERVAL '1 day') AS g(d)
)
SELECT d.day, COUNT(DISTINCT e.customer_id) AS active_customers
FROM days d LEFT JOIN events e
ON e.event_type = 'active'
AND e.occurred_at >= ((d.day - 6)::timestamp AT TIME ZONE 'UTC')
AND e.occurred_at < ((d.day + 1)::timestamp AT TIME ZONE 'UTC')
GROUP BY d.day ORDER BY
Each window includes the report date and six preceding dates. Summing seven daily distinct counts would double-count customers active on several days. The source scan must include September activity for the first October window. This range join emphasizes exact semantics; for long horizons and large event volumes, evaluate a deduplicated customer-day intermediate representation and indexes before assuming it scales. Approximate sketches would change the accuracy contract.
90. How would you measure the time to the next different event type while ignoring repeated identical events?
For each event, find the earliest later event with a different type using a lateral lookup. PostgreSQL timestamp subtraction gives the elapsed interval. Events with the same timestamp use event ID as their sequence tie-breaker.
SELECT e.event_id, e.event_type, n.event_id AS next_different_id,
n.occurred_at - e.occurred_at AS elapsed
FROM events e
LEFT JOIN LATERAL (
SELECT event_id, occurred_at FROM events x
WHERE x.customer_id = e.customer_id
AND x.event_type <> e.event_type
AND (x.occurred_at, x.event_id) > (e.occurred_at, e.event_id)
ORDER BY x.occurred_at, x.event_id LIMIT 1
) n ON TRUE
ORDER BY e.customer_id, e.occurred_at,
SELECT e.event_id, e.event_type, n.event_id AS next_different_id,
n.occurred_at - e.occurred_at AS elapsed
FROM events e
LEFT JOIN LATERAL (
SELECT event_id, occurred_at FROM events x
WHERE x.customer_id = e.customer_id
AND x.event_type <> e.event_type
AND (x.occurred_at, x.event_id) > (e.occurred_at, e.event_id)
ORDER BY x.occurred_at, x.event_id LIMIT 1
) n ON TRUE
ORDER BY e.customer_id, e.occurred_at,
SELECT e.event_id, e.event_type, n.event_id AS next_different_id,
n.occurred_at - e.occurred_at AS elapsed
FROM events e
LEFT JOIN LATERAL (
SELECT event_id, occurred_at FROM events x
WHERE x.customer_id = e.customer_id
AND x.event_type <> e.event_type
AND (x.occurred_at, x.event_id) > (e.occurred_at, e.event_id)
ORDER BY x.occurred_at, x.event_id LIMIT 1
) n ON TRUE
ORDER BY e.customer_id, e.occurred_at,
The result retains every source event and returns NULL when no later different type exists. A plain LEAD would inspect only the adjacent event, not skip a run of repeated types. The contract makes type non-NULL; nullable types would require an explicit NULL equality policy, such as PostgreSQL's IS DISTINCT FROM. A zero elapsed interval is legitimate when differently typed events share a timestamp.
91. How would you match each order to the price that was effective when it was placed?
Choose the latest price version whose effective time is not after the order time. The auxiliary table is price_history(product_id, effective_at timestamptz, version_id bigint primary key, price numeric), with non-NULL effective times and prices. PostgreSQL lateral lookup preserves an unmatched order line.
SELECT i.order_id, i.line_no, p.price AS historical_price
FROM order_items i JOIN orders o ON o.order_id = i.order_id
LEFT JOIN LATERAL (
SELECT h.price FROM price_history h
WHERE h.product_id = i.product_id AND h.effective_at <= o.ordered_at
ORDER BY h.effective_at DESC, h.version_id DESC LIMIT 1
) p ON TRUE
ORDER BY i.order_id,
SELECT i.order_id, i.line_no, p.price AS historical_price
FROM order_items i JOIN orders o ON o.order_id = i.order_id
LEFT JOIN LATERAL (
SELECT h.price FROM price_history h
WHERE h.product_id = i.product_id AND h.effective_at <= o.ordered_at
ORDER BY h.effective_at DESC, h.version_id DESC LIMIT 1
) p ON TRUE
ORDER BY i.order_id,
SELECT i.order_id, i.line_no, p.price AS historical_price
FROM order_items i JOIN orders o ON o.order_id = i.order_id
LEFT JOIN LATERAL (
SELECT h.price FROM price_history h
WHERE h.product_id = i.product_id AND h.effective_at <= o.ordered_at
ORDER BY h.effective_at DESC, h.version_id DESC LIMIT 1
) p ON TRUE
ORDER BY i.order_id,
The output grain is one order line; the largest version ID resolves versions sharing an effective time. A missing prior price returns NULL and should be investigated, not replaced with today's price. This is a temporal lookup exercise: actual charged revenue should still use the recorded unit_price, because discounts and negotiated prices may differ from catalog history. A product/effective-time index supports the lookup pattern without guaranteeing its plan.
92. How would you detect overlapping validity intervals for the same entity?
Self-join each interval to later IDs and use the half-open overlap condition. Assume validity(interval_id bigint primary key, entity_id integer not null, valid_from timestamptz not null, valid_to timestamptz) and enforce valid_to > valid_from whenever the end is not NULL. NULL means open-ended.
SELECT a.entity_id, a.interval_id AS first_id, b.interval_id AS second_id
FROM validity a JOIN validity b
ON b.entity_id = a.entity_id AND b.interval_id > a.interval_id
AND (a.valid_to IS NULL OR b.valid_from < a.valid_to)
AND (b.valid_to IS NULL OR a.valid_from < b.valid_to)
ORDER BY a.entity_id, a.interval_id,
SELECT a.entity_id, a.interval_id AS first_id, b.interval_id AS second_id
FROM validity a JOIN validity b
ON b.entity_id = a.entity_id AND b.interval_id > a.interval_id
AND (a.valid_to IS NULL OR b.valid_from < a.valid_to)
AND (b.valid_to IS NULL OR a.valid_from < b.valid_to)
ORDER BY a.entity_id, a.interval_id,
SELECT a.entity_id, a.interval_id AS first_id, b.interval_id AS second_id
FROM validity a JOIN validity b
ON b.entity_id = a.entity_id AND b.interval_id > a.interval_id
AND (a.valid_to IS NULL OR b.valid_from < a.valid_to)
AND (b.valid_to IS NULL OR a.valid_from < b.valid_to)
ORDER BY a.entity_id, a.interval_id,
Each overlapping pair appears once. Adjacent intervals such as [10:00,11:00) and [11:00,12:00) do not overlap. Merely comparing an interval with the immediately preceding start can miss containment across several rows. This query audits existing data; it does not prevent a concurrent insert from creating a new overlap. Prevention needs an appropriate database constraint or coordinated write protocol, not a check-then-insert application race.
93. How would you calculate peak concurrent bookings without counting adjacent bookings as overlapping?
Represent every start as +1 and every end as -1, combine changes at equal timestamps, and compute their cumulative sum. Assume finite, non-NULL half-open intervals in bookings(booking_id, starts_at, ends_at) with starts_at < ends_at.
WITH changes AS (
SELECT starts_at AS at, 1 AS delta FROM bookings
UNION ALL
SELECT ends_at, -1 FROM bookings
), net AS (
SELECT at, SUM(delta) AS delta FROM changes GROUP BY at
), concurrency AS (
SELECT at, SUM(delta) OVER (
ORDER BY at ROWS UNBOUNDED PRECEDING
) AS active FROM net
)
SELECT COALESCE(MAX(active), 0) AS peak_bookings FROM
WITH changes AS (
SELECT starts_at AS at, 1 AS delta FROM bookings
UNION ALL
SELECT ends_at, -1 FROM bookings
), net AS (
SELECT at, SUM(delta) AS delta FROM changes GROUP BY at
), concurrency AS (
SELECT at, SUM(delta) OVER (
ORDER BY at ROWS UNBOUNDED PRECEDING
) AS active FROM net
)
SELECT COALESCE(MAX(active), 0) AS peak_bookings FROM
WITH changes AS (
SELECT starts_at AS at, 1 AS delta FROM bookings
UNION ALL
SELECT ends_at, -1 FROM bookings
), net AS (
SELECT at, SUM(delta) AS delta FROM changes GROUP BY at
), concurrency AS (
SELECT at, SUM(delta) OVER (
ORDER BY at ROWS UNBOUNDED PRECEDING
) AS active FROM net
)
SELECT COALESCE(MAX(active), 0) AS peak_bookings FROM
The query returns a single peak count, including zero for an empty table. Grouping simultaneous boundaries applies the net change before measuring the following interval, so an ending booking and a starting booking do not create an artificial spike. UNION ALL is necessary because equal boundaries from different bookings are separate contributions. Resource-specific peaks require carrying the resource key through grouping and window partitioning.
94. How would you find gaps between a customer's paid orders that exceed ninety elapsed days?
Order paid orders by timestamp and ID, then compare each order to its predecessor. PostgreSQL's INTERVAL '2160 hours' explicitly requests ninety elapsed 24-hour periods rather than a local-calendar-day interpretation.
WITH sequence AS (
SELECT customer_id, order_id, ordered_at,
LAG(ordered_at) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS previous_at
FROM orders WHERE status = 'paid'
)
SELECT customer_id, order_id, previous_at, ordered_at,
ordered_at - previous_at AS elapsed
FROM sequence
WHERE ordered_at > previous_at + INTERVAL '2160 hours'
ORDER BY customer_id, ordered_at,
WITH sequence AS (
SELECT customer_id, order_id, ordered_at,
LAG(ordered_at) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS previous_at
FROM orders WHERE status = 'paid'
)
SELECT customer_id, order_id, previous_at, ordered_at,
ordered_at - previous_at AS elapsed
FROM sequence
WHERE ordered_at > previous_at + INTERVAL '2160 hours'
ORDER BY customer_id, ordered_at,
WITH sequence AS (
SELECT customer_id, order_id, ordered_at,
LAG(ordered_at) OVER (
PARTITION BY customer_id ORDER BY ordered_at, order_id
) AS previous_at
FROM orders WHERE status = 'paid'
)
SELECT customer_id, order_id, previous_at, ordered_at,
ordered_at - previous_at AS elapsed
FROM sequence
WHERE ordered_at > previous_at + INTERVAL '2160 hours'
ORDER BY customer_id, ordered_at,
Each output row identifies the order ending a qualifying gap. Customers with fewer than two paid orders have no result, and a gap exactly ninety days is excluded. Tied order timestamps create a zero gap for later tied rows. This does not detect current inactivity after the last order; that is a different query requiring an explicit observation cutoff and a decision about how customers without orders are classified.
95. How would you allocate an order-level discount proportionally across lines while preserving the exact cent total?
Use largest-remainder allocation on integer cents: give each line its floor share, then distribute remaining cents by descending remainder. Assume allocation_lines(line_no primary key, gross_cents bigint not null) has nonnegative values, positive total gross, and a fixed five-cent discount no larger than gross. PostgreSQL numeric integer-quotient and remainder functions avoid rounding a fractional share before deciding its floor.
WITH base AS (
SELECT line_no, 5::numeric * gross_cents AS numerator,
SUM(gross_cents) OVER () AS total_gross
FROM allocation_lines
), floors AS (
SELECT line_no, div(numerator, total_gross) AS cents,
mod(numerator, total_gross) AS remainder
FROM base
), ranked AS (
SELECT *, ROW_NUMBER() OVER (
ORDER BY remainder DESC, line_no
) AS rn, 5 - SUM(cents) OVER () AS remaining FROM floors
)
SELECT line_no, cents + CASE WHEN rn <= remaining THEN 1 ELSE 0 END AS discount_cents
FROM ranked ORDER BY
WITH base AS (
SELECT line_no, 5::numeric * gross_cents AS numerator,
SUM(gross_cents) OVER () AS total_gross
FROM allocation_lines
), floors AS (
SELECT line_no, div(numerator, total_gross) AS cents,
mod(numerator, total_gross) AS remainder
FROM base
), ranked AS (
SELECT *, ROW_NUMBER() OVER (
ORDER BY remainder DESC, line_no
) AS rn, 5 - SUM(cents) OVER () AS remaining FROM floors
)
SELECT line_no, cents + CASE WHEN rn <= remaining THEN 1 ELSE 0 END AS discount_cents
FROM ranked ORDER BY
WITH base AS (
SELECT line_no, 5::numeric * gross_cents AS numerator,
SUM(gross_cents) OVER () AS total_gross
FROM allocation_lines
), floors AS (
SELECT line_no, div(numerator, total_gross) AS cents,
mod(numerator, total_gross) AS remainder
FROM base
), ranked AS (
SELECT *, ROW_NUMBER() OVER (
ORDER BY remainder DESC, line_no
) AS rn, 5 - SUM(cents) OVER () AS remaining FROM floors
)
SELECT line_no, cents + CASE WHEN rn <= remaining THEN 1 ELSE 0 END AS discount_cents
FROM ranked ORDER BY
All lines share one denominator, so ranking exact remainders ranks their fractional shares. The line number breaks ties. Independent rounding can lose or create cents; this method preserves the total. Reject negative discounts, zero-total orders, and invalid inputs before allocation. For multiple orders, partition every aggregate and rank by order ID and supply each order's discount consistently.
96. How would you find the payment that first brings an order's cumulative receipts up to its total?
Calculate the payment balance in deterministic posting order, then select the first qualifying payment per order. This measures the first historical crossing, even if a later negative payment reverses it; it does not assert that the order is currently fully paid.
WITH balances AS (
SELECT p.*, SUM(amount) OVER (
PARTITION BY order_id ORDER BY paid_at, payment_id
ROWS UNBOUNDED PRECEDING
) AS received
FROM payments p
), crossings AS (
SELECT b.*, ROW_NUMBER() OVER (
PARTITION BY b.order_id ORDER BY b.paid_at, b.payment_id
) AS crossing_number
FROM balances b JOIN orders o ON o.order_id = b.order_id
WHERE o.total > 0 AND b.received >= o.total
)
SELECT order_id, payment_id, received
FROM crossings WHERE crossing_number = 1 ORDER BY
WITH balances AS (
SELECT p.*, SUM(amount) OVER (
PARTITION BY order_id ORDER BY paid_at, payment_id
ROWS UNBOUNDED PRECEDING
) AS received
FROM payments p
), crossings AS (
SELECT b.*, ROW_NUMBER() OVER (
PARTITION BY b.order_id ORDER BY b.paid_at, b.payment_id
) AS crossing_number
FROM balances b JOIN orders o ON o.order_id = b.order_id
WHERE o.total > 0 AND b.received >= o.total
)
SELECT order_id, payment_id, received
FROM crossings WHERE crossing_number = 1 ORDER BY
WITH balances AS (
SELECT p.*, SUM(amount) OVER (
PARTITION BY order_id ORDER BY paid_at, payment_id
ROWS UNBOUNDED PRECEDING
) AS received
FROM payments p
), crossings AS (
SELECT b.*, ROW_NUMBER() OVER (
PARTITION BY b.order_id ORDER BY b.paid_at, b.payment_id
) AS crossing_number
FROM balances b JOIN orders o ON o.order_id = b.order_id
WHERE o.total > 0 AND b.received >= o.total
)
SELECT order_id, payment_id, received
FROM crossings WHERE crossing_number = 1 ORDER BY
The result has at most one row per positive-total order. Unpaid and zero-total orders are absent because no receipt-triggered crossing is defined for them here. Payment IDs break simultaneous posting ties. Filtering out negative payments before calculating the balance would be incorrect. Backdated receipts can change the historical crossing, so use an immutable posting sequence instead if accounting policy defines order by posting rather than effective payment time.
97. How would you compare two snapshots as multisets rather than just sets?
Group each snapshot by its complete business tuple, assigning positive counts to the old snapshot and negative counts to the new one, then retain nonzero differences. These inline snapshots deliberately include duplicates and NULL values.
WITH old_rows(code, amount) AS (VALUES ('A',10),('A',10),('B',NULL)),
new_rows(code, amount) AS (VALUES ('A',10),('B',NULL),('C',5)),
signed AS (
SELECT code, amount, COUNT(*) AS delta FROM old_rows GROUP BY code, amount
UNION ALL
SELECT code, amount, -COUNT(*) FROM new_rows GROUP BY code, amount
)
SELECT code, amount, SUM(delta) AS old_minus_new
FROM signed GROUP BY code, amount HAVING SUM(delta) <> 0
ORDER BY
WITH old_rows(code, amount) AS (VALUES ('A',10),('A',10),('B',NULL)),
new_rows(code, amount) AS (VALUES ('A',10),('B',NULL),('C',5)),
signed AS (
SELECT code, amount, COUNT(*) AS delta FROM old_rows GROUP BY code, amount
UNION ALL
SELECT code, amount, -COUNT(*) FROM new_rows GROUP BY code, amount
)
SELECT code, amount, SUM(delta) AS old_minus_new
FROM signed GROUP BY code, amount HAVING SUM(delta) <> 0
ORDER BY
WITH old_rows(code, amount) AS (VALUES ('A',10),('A',10),('B',NULL)),
new_rows(code, amount) AS (VALUES ('A',10),('B',NULL),('C',5)),
signed AS (
SELECT code, amount, COUNT(*) AS delta FROM old_rows GROUP BY code, amount
UNION ALL
SELECT code, amount, -COUNT(*) FROM new_rows GROUP BY code, amount
)
SELECT code, amount, SUM(delta) AS old_minus_new
FROM signed GROUP BY code, amount HAVING SUM(delta) <> 0
ORDER BY
The result reports one missing ('A',10) occurrence and one added ('C',5) occurrence; the NULL-bearing tuple matches through grouping semantics. A plain EXCEPT would lose duplicate multiplicity. This representation reports tuple-count changes rather than attributing them to updates of identified entities. Compare all relevant columns, and do not substitute an unchecked hash if collision-free equality is required.
98. How would you compare nullable attributes without missing changes?
Use NULL-safe inequality rather than <>. PostgreSQL supports IS DISTINCT FROM, which treats two NULLs as equal and exactly one NULL as different. Here old and new tables contain one row per customer with the same non-NULL primary key.
WITH old_profile(customer_id, country) AS (
VALUES (1,'US'),(2,NULL),(3,'CA')
), new_profile(customer_id, country) AS (
VALUES (1,'US'),(2,'GB'),(3,NULL)
)
SELECT o.customer_id, o.country AS old_country, n.country AS new_country
FROM old_profile o JOIN new_profile n USING (customer_id)
WHERE o.country IS DISTINCT FROM n.country
ORDER BY
WITH old_profile(customer_id, country) AS (
VALUES (1,'US'),(2,NULL),(3,'CA')
), new_profile(customer_id, country) AS (
VALUES (1,'US'),(2,'GB'),(3,NULL)
)
SELECT o.customer_id, o.country AS old_country, n.country AS new_country
FROM old_profile o JOIN new_profile n USING (customer_id)
WHERE o.country IS DISTINCT FROM n.country
ORDER BY
WITH old_profile(customer_id, country) AS (
VALUES (1,'US'),(2,NULL),(3,'CA')
), new_profile(customer_id, country) AS (
VALUES (1,'US'),(2,'GB'),(3,NULL)
)
SELECT o.customer_id, o.country AS old_country, n.country AS new_country
FROM old_profile o JOIN new_profile n USING (customer_id)
WHERE o.country IS DISTINCT FROM n.country
ORDER BY
Customers 2 and 3 are returned; customer 1 is unchanged. A predicate using ordinary inequality would evaluate to unknown for the two changes and return neither. This query compares only keys present in both snapshots. Detecting additions and deletions requires a full outer join with presence markers or separate anti-joins. Do not replace NULL with an arbitrary sentinel string unless the domain guarantees that sentinel can never be a real value.
99. How would you audit composite foreign keys when partially NULL keys must be rejected?
Apply MATCH FULL-style semantics: all key components NULL means no reference, all components non-NULL must match a parent, and partially NULL keys are invalid. These inline fixtures distinguish partial-NULL failures from missing parent keys.
WITH parent(tenant_id, account_id) AS (VALUES (1,10),(2,20)),
child(row_id, tenant_id, account_id) AS (
VALUES (1,1,10),(2,1,NULL),(3,NULL,NULL),(4,2,99)
)
SELECT c.row_id,
CASE WHEN c.tenant_id IS NULL OR c.account_id IS NULL
THEN 'partial_null' ELSE 'missing_parent' END AS issue
FROM child c
WHERE (c.tenant_id IS NULL AND c.account_id IS NOT NULL)
OR (c.tenant_id IS NOT NULL AND c.account_id IS NULL)
OR (c.tenant_id IS NOT NULL AND c.account_id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM parent p
WHERE p.tenant_id = c.tenant_id AND p.account_id = c.account_id))
ORDER BY
WITH parent(tenant_id, account_id) AS (VALUES (1,10),(2,20)),
child(row_id, tenant_id, account_id) AS (
VALUES (1,1,10),(2,1,NULL),(3,NULL,NULL),(4,2,99)
)
SELECT c.row_id,
CASE WHEN c.tenant_id IS NULL OR c.account_id IS NULL
THEN 'partial_null' ELSE 'missing_parent' END AS issue
FROM child c
WHERE (c.tenant_id IS NULL AND c.account_id IS NOT NULL)
OR (c.tenant_id IS NOT NULL AND c.account_id IS NULL)
OR (c.tenant_id IS NOT NULL AND c.account_id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM parent p
WHERE p.tenant_id = c.tenant_id AND p.account_id = c.account_id))
ORDER BY
WITH parent(tenant_id, account_id) AS (VALUES (1,10),(2,20)),
child(row_id, tenant_id, account_id) AS (
VALUES (1,1,10),(2,1,NULL),(3,NULL,NULL),(4,2,99)
)
SELECT c.row_id,
CASE WHEN c.tenant_id IS NULL OR c.account_id IS NULL
THEN 'partial_null' ELSE 'missing_parent' END AS issue
FROM child c
WHERE (c.tenant_id IS NULL AND c.account_id IS NOT NULL)
OR (c.tenant_id IS NOT NULL AND c.account_id IS NULL)
OR (c.tenant_id IS NOT NULL AND c.account_id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM parent p
WHERE p.tenant_id = c.tenant_id AND p.account_id = c.account_id))
ORDER BY
Rows 2 and 4 are reported; row 3 intentionally represents no reference. Joining only on account ID would confuse tenants. The audit is useful for imported staging data, but a database foreign-key constraint is the appropriate ongoing integrity mechanism. Default MATCH SIMPLE semantics differ: a partial-NULL reference can avoid the parent lookup, so the intended policy must be specified rather than inferred.
100. How would you find candidate-role matches when every required skill has a minimum proficiency?
Cross join candidates to roles and reject a pair if any requirement lacks a sufficient candidate skill. This is relational division over a composite role-skill requirement, with a threshold comparison rather than mere membership.
WITH candidates(candidate_id) AS (VALUES (1),(2)),
roles(role_id) AS (VALUES (10),(20)),
requirements(role_id, skill, minimum_level) AS (
VALUES (10,'sql',3),(10,'python',2)
), skills(candidate_id, skill, proficiency) AS (
VALUES (1,'sql',4),(1,'python',2),(2,'sql',2)
)
SELECT c.candidate_id, r.role_id
FROM candidates c CROSS JOIN roles r
WHERE NOT EXISTS (
SELECT 1 FROM requirements q WHERE q.role_id = r.role_id
AND NOT EXISTS (
SELECT 1 FROM skills s
WHERE s.candidate_id = c.candidate_id AND s.skill = q.skill
AND s.proficiency >= q.minimum_level
)
)
ORDER BY c.candidate_id,
WITH candidates(candidate_id) AS (VALUES (1),(2)),
roles(role_id) AS (VALUES (10),(20)),
requirements(role_id, skill, minimum_level) AS (
VALUES (10,'sql',3),(10,'python',2)
), skills(candidate_id, skill, proficiency) AS (
VALUES (1,'sql',4),(1,'python',2),(2,'sql',2)
)
SELECT c.candidate_id, r.role_id
FROM candidates c CROSS JOIN roles r
WHERE NOT EXISTS (
SELECT 1 FROM requirements q WHERE q.role_id = r.role_id
AND NOT EXISTS (
SELECT 1 FROM skills s
WHERE s.candidate_id = c.candidate_id AND s.skill = q.skill
AND s.proficiency >= q.minimum_level
)
)
ORDER BY c.candidate_id,
WITH candidates(candidate_id) AS (VALUES (1),(2)),
roles(role_id) AS (VALUES (10),(20)),
requirements(role_id, skill, minimum_level) AS (
VALUES (10,'sql',3),(10,'python',2)
), skills(candidate_id, skill, proficiency) AS (
VALUES (1,'sql',4),(1,'python',2),(2,'sql',2)
)
SELECT c.candidate_id, r.role_id
FROM candidates c CROSS JOIN roles r
WHERE NOT EXISTS (
SELECT 1 FROM requirements q WHERE q.role_id = r.role_id
AND NOT EXISTS (
SELECT 1 FROM skills s
WHERE s.candidate_id = c.candidate_id AND s.skill = q.skill
AND s.proficiency >= q.minimum_level
)
)
ORDER BY c.candidate_id,
Candidate 1 qualifies for role 10, and both candidates qualify for requirement-free role 20 by vacuous truth. If empty roles should be excluded, add an explicit requirement existence check. Extra candidate skills are allowed. In a real schema, enforce one current proficiency per candidate-skill pair and non-NULL requirements; otherwise duplicate versions or unknown thresholds need an explicit interpretation.
101. How would you fetch the next page of orders without a large OFFSET?
Use the last row's complete ordering key as a cursor and seek strictly beyond it. This PostgreSQL example pages newest first with a page size of twenty and assumes the preceding page ended at the displayed timestamp and order ID.
SELECT order_id, customer_id, ordered_at, total
FROM orders
WHERE (ordered_at, order_id) <
(TIMESTAMPTZ '2026-10-15 12:00:00+00', 500)
ORDER BY ordered_at DESC, order_id DESC
LIMIT 20
SELECT order_id, customer_id, ordered_at, total
FROM orders
WHERE (ordered_at, order_id) <
(TIMESTAMPTZ '2026-10-15 12:00:00+00', 500)
ORDER BY ordered_at DESC, order_id DESC
LIMIT 20
SELECT order_id, customer_id, ordered_at, total
FROM orders
WHERE (ordered_at, order_id) <
(TIMESTAMPTZ '2026-10-15 12:00:00+00', 500)
ORDER BY ordered_at DESC, order_id DESC
LIMIT 20
The unique ID makes timestamp ties deterministic; a timestamp-only cursor could skip tied orders. Both cursor fields are non-NULL under the schema. An index beginning with the ordering columns can support the seek, but a plan should verify actual behavior. Keyset pagination does not itself freeze a changing dataset: updated sort keys can move records across pages, and new records may appear above the cursor. Use an appropriate snapshot or immutable export boundary when consistent traversal is required.
102. How would you page customers by a nullable country without losing NULL-valued rows?
Normalize NULL placement into a non-NULL tuple, then use exactly that expression for both the cursor and ordering. This example sorts actual countries first and NULL countries last, with customer ID as the final unique tie-breaker.
SELECT customer_id, country
FROM customers
WHERE (CASE WHEN country IS NULL THEN 1 ELSE 0 END,
COALESCE(country, ''), customer_id) > (0, 'US', 42)
ORDER BY CASE WHEN country IS NULL THEN 1 ELSE 0 END,
COALESCE(country, ''), customer_id
LIMIT 20
SELECT customer_id, country
FROM customers
WHERE (CASE WHEN country IS NULL THEN 1 ELSE 0 END,
COALESCE(country, ''), customer_id) > (0, 'US', 42)
ORDER BY CASE WHEN country IS NULL THEN 1 ELSE 0 END,
COALESCE(country, ''), customer_id
LIMIT 20
SELECT customer_id, country
FROM customers
WHERE (CASE WHEN country IS NULL THEN 1 ELSE 0 END,
COALESCE(country, ''), customer_id) > (0, 'US', 42)
ORDER BY CASE WHEN country IS NULL THEN 1 ELSE 0 END,
COALESCE(country, ''), customer_id
LIMIT 20
The cursor represents customer 42 in country US. Once inside the NULL group, use (1, '', last_id) instead. The empty-string replacement does not collide with real empty countries because the preceding flag separates the groups. Text comparison follows the database collation, which must remain consistent between pages. A plain row comparison containing NULL would yield unknown in relevant cases and omit rows. An expression index may be useful for this exact ordering, but it must match the chosen expressions and collation.
103. How would you split an OR filter into UNION ALL branches without duplicating rows that satisfy both conditions?
Make the second branch exclude the first branch's population. The original requirement is orders for customer 1 or orders worth at least eighty. PostgreSQL's NULL-safe comparison also preserves qualifying high-value orders if this pattern is adapted to a staging schema allowing unknown customers; the shared schema forbids them.
SELECT order_id, customer_id, total
FROM orders WHERE customer_id = 1
UNION ALL
SELECT order_id, customer_id, total
FROM orders
WHERE total >= 80 AND customer_id IS DISTINCT FROM 1
ORDER BY
SELECT order_id, customer_id, total
FROM orders WHERE customer_id = 1
UNION ALL
SELECT order_id, customer_id, total
FROM orders
WHERE total >= 80 AND customer_id IS DISTINCT FROM 1
ORDER BY
SELECT order_id, customer_id, total
FROM orders WHERE customer_id = 1
UNION ALL
SELECT order_id, customer_id, total
FROM orders
WHERE total >= 80 AND customer_id IS DISTINCT FROM 1
ORDER BY
An eighty-unit order for customer 1 appears only once. Naively repeating both predicates in independent UNION ALL branches would duplicate that order, while UNION would add deduplication work and could change multiplicities if the projection omitted the unique key. This is an optimization candidate, not a claim that it beats the original OR: PostgreSQL may already choose efficient bitmap or index paths. Compare complete results and representative plans before adopting it, and preserve NULL logic when generalizing the disjointness predicate.
104. How would you express a New York business day without assuming it always lasts twenty-four hours?
Convert each local midnight separately to a timestamp with time zone, then filter between them. This PostgreSQL exercise uses the November 1, 2026 daylight-saving transition in America/New_York.
SELECT order_id, ordered_at
FROM orders
WHERE ordered_at >= (TIMESTAMP '2026-11-01 00:00:00'
AT TIME ZONE 'America/New_York')
AND ordered_at < (TIMESTAMP '2026-11-02 00:00:00'
AT TIME ZONE 'America/New_York')
ORDER BY ordered_at,
SELECT order_id, ordered_at
FROM orders
WHERE ordered_at >= (TIMESTAMP '2026-11-01 00:00:00'
AT TIME ZONE 'America/New_York')
AND ordered_at < (TIMESTAMP '2026-11-02 00:00:00'
AT TIME ZONE 'America/New_York')
ORDER BY ordered_at,
SELECT order_id, ordered_at
FROM orders
WHERE ordered_at >= (TIMESTAMP '2026-11-01 00:00:00'
AT TIME ZONE 'America/New_York')
AND ordered_at < (TIMESTAMP '2026-11-02 00:00:00'
AT TIME ZONE 'America/New_York')
ORDER BY ordered_at,
Both repeated occurrences of a local clock time during the fall-back hour are included because orders store absolute instants. Adding twenty-four elapsed hours to the first boundary would not represent this whole local date. Named-zone rules depend on the installed timezone database, so production systems should keep that data current. This query establishes date membership, not a display format; convert the returned values only when presenting local timestamps, and retain offset information when ambiguity matters.
105. How would you demonstrate why replacing NOT EXISTS with NOT IN can silently remove every unmatched row?
Use a fixture containing a NULL in the exclusion list and compare both predicates directly. The intended rule excludes matching known customer IDs; an unknown exclusion ID is not a match for every customer.
WITH candidates(customer_id) AS (VALUES (1),(2),(3)),
excluded(customer_id) AS (VALUES (2),(NULL))
SELECT c.customer_id,
c.customer_id NOT IN (SELECT customer_id FROM excluded) AS not_in_result,
NOT EXISTS (SELECT 1 FROM excluded e
WHERE e.customer_id = c.customer_id) AS not_exists_result
FROM candidates c ORDER BY
WITH candidates(customer_id) AS (VALUES (1),(2),(3)),
excluded(customer_id) AS (VALUES (2),(NULL))
SELECT c.customer_id,
c.customer_id NOT IN (SELECT customer_id FROM excluded) AS not_in_result,
NOT EXISTS (SELECT 1 FROM excluded e
WHERE e.customer_id = c.customer_id) AS not_exists_result
FROM candidates c ORDER BY
WITH candidates(customer_id) AS (VALUES (1),(2),(3)),
excluded(customer_id) AS (VALUES (2),(NULL))
SELECT c.customer_id,
c.customer_id NOT IN (SELECT customer_id FROM excluded) AS not_in_result,
NOT EXISTS (SELECT 1 FROM excluded e
WHERE e.customer_id = c.customer_id) AS not_exists_result
FROM candidates c ORDER BY
For customers 1 and 3, not_in_result is NULL while not_exists_result is true; both are false for customer 2. A WHERE clause keeps only true, so the naive NOT IN filter returns no candidates. This is a concrete rewrite-equivalence test, not a universal ban on NOT IN: it is valid under suitable non-NULL guarantees. If NULL is intended to represent a real matching category, specify NULL-safe equality instead. The primary-key candidate IDs here are known, avoiding a second unresolved NULL policy.
106. How would you remove a join that exists only to require at least one matching child row?
Replace the multiplying join with a semi-join expressed through EXISTS. The task asks for one row per paid order that contains a product in the Books category, not one row per matching line.
SELECT o.order_id, o.total
FROM orders o
WHERE o.status = 'paid'
AND EXISTS (
SELECT 1 FROM order_items i
JOIN products p ON p.product_id = i.product_id
WHERE i.order_id = o.order_id AND p.category = 'Books'
)
ORDER BY
SELECT o.order_id, o.total
FROM orders o
WHERE o.status = 'paid'
AND EXISTS (
SELECT 1 FROM order_items i
JOIN products p ON p.product_id = i.product_id
WHERE i.order_id = o.order_id AND p.category = 'Books'
)
ORDER BY
SELECT o.order_id, o.total
FROM orders o
WHERE o.status = 'paid'
AND EXISTS (
SELECT 1 FROM order_items i
JOIN products p ON p.product_id = i.product_id
WHERE i.order_id = o.order_id AND p.category = 'Books'
)
ORDER BY
Two book lines still produce one order row, and an order with no lines produces none. A DISTINCT over a broad joined result may hide multiplication after doing unnecessary work; EXISTS directly states the desired cardinality. This is not a universal performance claim: PostgreSQL may optimize either form well, and row counts, constraints, and indexes influence plans. If the output needs a matching product's attributes, define which product or aggregate them rather than applying this rewrite blindly.
107. How would you inspect the execution of an aggregate query without confusing estimated rows with actual rows?
Run PostgreSQL EXPLAIN (ANALYZE, BUFFERS) on the read-only query in a suitable test environment. It executes the statement, so it is not merely a syntax check or a safe wrapper for arbitrary writes.
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(total) AS paid_revenue
FROM orders
WHERE status = 'paid'
AND ordered_at >= TIMESTAMPTZ '2026-10-01 00:00:00+00'
AND ordered_at < TIMESTAMPTZ '2026-11-01 00:00:00+00'
GROUP BY
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(total) AS paid_revenue
FROM orders
WHERE status = 'paid'
AND ordered_at >= TIMESTAMPTZ '2026-10-01 00:00:00+00'
AND ordered_at < TIMESTAMPTZ '2026-11-01 00:00:00+00'
GROUP BY
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(total) AS paid_revenue
FROM orders
WHERE status = 'paid'
AND ordered_at >= TIMESTAMPTZ '2026-10-01 00:00:00+00'
AND ordered_at < TIMESTAMPTZ '2026-11-01 00:00:00+00'
GROUP BY
Compare estimated and actual cardinalities at selective scans and joins, account for loops, and inspect buffer activity and sort or hash spill indications. A plan from a tiny fixture cannot establish a production speedup. This article supplies no fabricated plan output, and this PostgreSQL-specific statement was reviewed rather than executed here. Benchmark representative data and parameters while controlling cache effects; adding an index or rewriting a CTE is a hypothesis to measure, not proof of improvement.
108. How would you count customers whose total paid revenue is below 100, including nonbuyers?
Aggregate paid revenue by customer first, then compare that total against the threshold after a left join. This example includes customers with no paid orders by treating their missing sum as zero.
WITH revenue AS (
SELECT customer_id, SUM(total) AS amount
FROM orders WHERE status = 'paid' GROUP BY customer_id
)
SELECT COUNT(*) AS customers_below_threshold
FROM customers c LEFT JOIN revenue r ON r.customer_id = c.customer_id
WHERE COALESCE(r.amount, 0) < 100
WITH revenue AS (
SELECT customer_id, SUM(total) AS amount
FROM orders WHERE status = 'paid' GROUP BY customer_id
)
SELECT COUNT(*) AS customers_below_threshold
FROM customers c LEFT JOIN revenue r ON r.customer_id = c.customer_id
WHERE COALESCE(r.amount, 0) < 100
WITH revenue AS (
SELECT customer_id, SUM(total) AS amount
FROM orders WHERE status = 'paid' GROUP BY customer_id
)
SELECT COUNT(*) AS customers_below_threshold
FROM customers c LEFT JOIN revenue r ON r.customer_id = c.customer_id
WHERE COALESCE(r.amount, 0) < 100
The output is one count, including zero for an empty customer population. Filtering individual orders with total < 100 answers a different question: a customer with two sixty-unit orders is not below a hundred in total revenue. No date restriction is implied; for a period threshold put its boundaries inside the revenue CTE. Because the schema makes totals nonnegative, zero has a straightforward interpretation here. Refund-adjusted net revenue would require a separate contract rather than silently reusing these order totals.
109. How would you find a minimum set of high-value orders reaching eighty percent of revenue?
Sort paid orders by descending value and a deterministic ID tie-breaker, then retain each row whose preceding cumulative total is still below the target. PostgreSQL numeric arithmetic makes the eighty-percent comparison explicit.
WITH ranked AS (
SELECT order_id, total,
SUM(total) OVER () AS all_revenue,
SUM(total) OVER (ORDER BY total DESC, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS before_revenue
FROM orders WHERE status = 'paid'
)
SELECT order_id, total
FROM ranked
WHERE all_revenue > 0
AND COALESCE(before_revenue, 0) < 0.8 * all_revenue
ORDER BY total DESC,
WITH ranked AS (
SELECT order_id, total,
SUM(total) OVER () AS all_revenue,
SUM(total) OVER (ORDER BY total DESC, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS before_revenue
FROM orders WHERE status = 'paid'
)
SELECT order_id, total
FROM ranked
WHERE all_revenue > 0
AND COALESCE(before_revenue, 0) < 0.8 * all_revenue
ORDER BY total DESC,
WITH ranked AS (
SELECT order_id, total,
SUM(total) OVER () AS all_revenue,
SUM(total) OVER (ORDER BY total DESC, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS before_revenue
FROM orders WHERE status = 'paid'
)
SELECT order_id, total
FROM ranked
WHERE all_revenue > 0
AND COALESCE(before_revenue, 0) < 0.8 * all_revenue
ORDER BY total DESC,
This includes the order crossing the target, unlike filtering cumulative totals at or below eighty percent. Descending nonnegative amounts minimize the number of selected orders; ties are deliberately broken rather than all included. An empty or all-zero population returns no rows because there is no positive target. If negative adjustments are admitted, the monotonic-prefix argument breaks and this solution no longer has the same guarantee.
110. How would you attribute revenue across overlapping tags without inflating the grand total?
Deduplicate each order's tags, give untagged orders one placeholder membership, and divide its total equally across the resulting memberships. PostgreSQL numeric division keeps fractional attribution instead of truncating to integers. The fixture has one double-tagged order and one untagged order.
WITH sales(order_id, amount) AS (VALUES (1,100),(2,30)),
tags(order_id, tag) AS (VALUES (1,'A'),(1,'B'),(1,'A')),
membership AS (
SELECT s.order_id, s.amount, t.tag,
COUNT(*) OVER (PARTITION BY s.order_id) AS memberships
FROM sales s LEFT JOIN (SELECT DISTINCT order_id, tag FROM tags) t
ON t.order_id = s.order_id
)
SELECT tag, SUM(amount::numeric / memberships) AS attributed_revenue
FROM membership GROUP BY tag ORDER BY tag NULLS LAST
WITH sales(order_id, amount) AS (VALUES (1,100),(2,30)),
tags(order_id, tag) AS (VALUES (1,'A'),(1,'B'),(1,'A')),
membership AS (
SELECT s.order_id, s.amount, t.tag,
COUNT(*) OVER (PARTITION BY s.order_id) AS memberships
FROM sales s LEFT JOIN (SELECT DISTINCT order_id, tag FROM tags) t
ON t.order_id = s.order_id
)
SELECT tag, SUM(amount::numeric / memberships) AS attributed_revenue
FROM membership GROUP BY tag ORDER BY tag NULLS LAST
WITH sales(order_id, amount) AS (VALUES (1,100),(2,30)),
tags(order_id, tag) AS (VALUES (1,'A'),(1,'B'),(1,'A')),
membership AS (
SELECT s.order_id, s.amount, t.tag,
COUNT(*) OVER (PARTITION BY s.order_id) AS memberships
FROM sales s LEFT JOIN (SELECT DISTINCT order_id, tag FROM tags) t
ON t.order_id = s.order_id
)
SELECT tag, SUM(amount::numeric / memberships) AS attributed_revenue
FROM membership GROUP BY tag ORDER BY tag NULLS LAST
The output is fifty for A, fifty for B, and thirty for the NULL untagged bucket. Non-NULL tag names are assumed, making that bucket unambiguous. Summing full order value for every tag would inflate overall revenue. Equal splitting is a business attribution choice, not a causal conclusion. Repeating fractions may require a cent-allocation policy before storing monetary amounts; do not independently round each membership and assume exact conservation.
111. How would you delete duplicate staging rows while retaining the latest complete record?
Rank each business key by descending update time and unique row ID, then delete ranks greater than one. This PostgreSQL demonstration operates only on a temporary fixture and rolls back; it never modifies the shared article tables.
BEGIN;
CREATE TEMP TABLE stage (
row_id integer PRIMARY KEY, external_key text NOT NULL,
updated_at timestamptz NOT NULL, payload text
);
INSERT INTO stage VALUES
(1,'A','2026-10-01 00:00+00','old'),
(2,'A','2026-10-02 00:00+00','new');
WITH ranked AS (
SELECT row_id, ROW_NUMBER() OVER (
PARTITION BY external_key ORDER BY updated_at DESC, row_id DESC
) AS rn FROM stage
)
DELETE FROM stage s USING ranked r
WHERE s.row_id = r.row_id AND r.rn > 1
RETURNING s.row_id;
SELECT * FROM stage;
ROLLBACK
BEGIN;
CREATE TEMP TABLE stage (
row_id integer PRIMARY KEY, external_key text NOT NULL,
updated_at timestamptz NOT NULL, payload text
);
INSERT INTO stage VALUES
(1,'A','2026-10-01 00:00+00','old'),
(2,'A','2026-10-02 00:00+00','new');
WITH ranked AS (
SELECT row_id, ROW_NUMBER() OVER (
PARTITION BY external_key ORDER BY updated_at DESC, row_id DESC
) AS rn FROM stage
)
DELETE FROM stage s USING ranked r
WHERE s.row_id = r.row_id AND r.rn > 1
RETURNING s.row_id;
SELECT * FROM stage;
ROLLBACK
BEGIN;
CREATE TEMP TABLE stage (
row_id integer PRIMARY KEY, external_key text NOT NULL,
updated_at timestamptz NOT NULL, payload text
);
INSERT INTO stage VALUES
(1,'A','2026-10-01 00:00+00','old'),
(2,'A','2026-10-02 00:00+00','new');
WITH ranked AS (
SELECT row_id, ROW_NUMBER() OVER (
PARTITION BY external_key ORDER BY updated_at DESC, row_id DESC
) AS rn FROM stage
)
DELETE FROM stage s USING ranked r
WHERE s.row_id = r.row_id AND r.rn > 1
RETURNING s.row_id;
SELECT * FROM stage;
ROLLBACK
Row 1 is deleted and row 2 remains during the transaction. Selecting the whole winning row avoids combining unrelated column maxima into a record that never existed. In a real import, define NULL-key policy, coordinate concurrent ingestion, and add the appropriate uniqueness constraint after cleanup. A deduplication query alone does not prevent future duplicates.
112. How would you upsert a snapshot without allowing an older update to overwrite a newer one?
Guard the conflict update with the incoming version. This PostgreSQL fixture uses ON CONFLICT against the primary key and accepts only strictly greater versions; repeating the same version makes no change.
BEGIN;
CREATE TEMP TABLE snapshot (
entity_id integer PRIMARY KEY, version bigint NOT NULL, payload text
);
INSERT INTO snapshot VALUES (1,5,'current');
INSERT INTO snapshot AS s VALUES (1,4,'stale'),(2,1,'new')
ON CONFLICT (entity_id) DO UPDATE
SET version = EXCLUDED.version, payload = EXCLUDED.payload
WHERE EXCLUDED.version > s.version
RETURNING entity_id, version, payload;
SELECT * FROM snapshot ORDER BY entity_id;
ROLLBACK
BEGIN;
CREATE TEMP TABLE snapshot (
entity_id integer PRIMARY KEY, version bigint NOT NULL, payload text
);
INSERT INTO snapshot VALUES (1,5,'current');
INSERT INTO snapshot AS s VALUES (1,4,'stale'),(2,1,'new')
ON CONFLICT (entity_id) DO UPDATE
SET version = EXCLUDED.version, payload = EXCLUDED.payload
WHERE EXCLUDED.version > s.version
RETURNING entity_id, version, payload;
SELECT * FROM snapshot ORDER BY entity_id;
ROLLBACK
BEGIN;
CREATE TEMP TABLE snapshot (
entity_id integer PRIMARY KEY, version bigint NOT NULL, payload text
);
INSERT INTO snapshot VALUES (1,5,'current');
INSERT INTO snapshot AS s VALUES (1,4,'stale'),(2,1,'new')
ON CONFLICT (entity_id) DO UPDATE
SET version = EXCLUDED.version, payload = EXCLUDED.payload
WHERE EXCLUDED.version > s.version
RETURNING entity_id, version, payload;
SELECT * FROM snapshot ORDER BY entity_id;
ROLLBACK
Entity 1 stays at version 5, and entity 2 is inserted. The version is an authoritative per-entity sequence, not an unreliable arrival timestamp. Equal-version payload disagreements require a separate validation policy; this query deliberately ignores them. Deduplicate a batch containing repeated entity keys before executing this multirow upsert, because PostgreSQL will not update the same target row twice in one command. All mutations here are confined to the temporary fixture.
113. How would you update order summaries safely when the source has multiple detail rows?
Aggregate the source to exactly one row per target key before using PostgreSQL UPDATE ... FROM. The fixture explicitly resets orders without detail rows to zero, instead of leaving stale totals behind.
BEGIN;
CREATE TEMP TABLE summary(order_id integer PRIMARY KEY, total numeric NOT NULL);
CREATE TEMP TABLE detail(order_id integer NOT NULL, amount numeric NOT NULL);
INSERT INTO summary VALUES (1,999),(2,999);
INSERT INTO detail VALUES (1,10),(1,20);
WITH totals AS (
SELECT s.order_id, COALESCE(SUM(d.amount),0) AS amount
FROM summary s LEFT JOIN detail d ON d.order_id = s.order_id
GROUP BY s.order_id
)
UPDATE summary s SET total = t.amount
FROM totals t WHERE t.order_id = s.order_id
RETURNING s.order_id, s.total;
ROLLBACK
BEGIN;
CREATE TEMP TABLE summary(order_id integer PRIMARY KEY, total numeric NOT NULL);
CREATE TEMP TABLE detail(order_id integer NOT NULL, amount numeric NOT NULL);
INSERT INTO summary VALUES (1,999),(2,999);
INSERT INTO detail VALUES (1,10),(1,20);
WITH totals AS (
SELECT s.order_id, COALESCE(SUM(d.amount),0) AS amount
FROM summary s LEFT JOIN detail d ON d.order_id = s.order_id
GROUP BY s.order_id
)
UPDATE summary s SET total = t.amount
FROM totals t WHERE t.order_id = s.order_id
RETURNING s.order_id, s.total;
ROLLBACK
BEGIN;
CREATE TEMP TABLE summary(order_id integer PRIMARY KEY, total numeric NOT NULL);
CREATE TEMP TABLE detail(order_id integer NOT NULL, amount numeric NOT NULL);
INSERT INTO summary VALUES (1,999),(2,999);
INSERT INTO detail VALUES (1,10),(1,20);
WITH totals AS (
SELECT s.order_id, COALESCE(SUM(d.amount),0) AS amount
FROM summary s LEFT JOIN detail d ON d.order_id = s.order_id
GROUP BY s.order_id
)
UPDATE summary s SET total = t.amount
FROM totals t WHERE t.order_id = s.order_id
RETURNING s.order_id, s.total;
ROLLBACK
The resulting totals are thirty and zero; RETURNING has no guaranteed row order. Joining raw detail rows directly would provide multiple potential source values for one update and would not define the intended sum. The exercise assumes the detail snapshot is the authoritative complete source. Production synchronization must also define concurrent write behavior and avoid overwriting unrelated summary components such as tax or shipping.
114. How would you decrement inventory without allowing a single-row stock value to become negative?
Put the availability check and decrement in the same conditional update, and inspect whether it returned a row. This PostgreSQL example reserves three units from a temporary stock row containing five.
BEGIN;
CREATE TEMP TABLE stock (
product_id integer PRIMARY KEY,
available integer NOT NULL CHECK (available >= 0)
);
INSERT INTO stock VALUES (1,5);
UPDATE stock
SET available = available - 3
WHERE product_id = 1 AND available >= 3
RETURNING product_id, available;
ROLLBACK
BEGIN;
CREATE TEMP TABLE stock (
product_id integer PRIMARY KEY,
available integer NOT NULL CHECK (available >= 0)
);
INSERT INTO stock VALUES (1,5);
UPDATE stock
SET available = available - 3
WHERE product_id = 1 AND available >= 3
RETURNING product_id, available;
ROLLBACK
BEGIN;
CREATE TEMP TABLE stock (
product_id integer PRIMARY KEY,
available integer NOT NULL CHECK (available >= 0)
);
INSERT INTO stock VALUES (1,5);
UPDATE stock
SET available = available - 3
WHERE product_id = 1 AND available >= 3
RETURNING product_id, available;
ROLLBACK
The fixture returns availability two; no returned row would mean the key was absent or insufficient stock met the predicate. Combining the predicate with the write avoids a separate read-then-write race on this row. The requested quantity must be validated as positive. This is not a complete reservation system: retries need an idempotency key, cancellations need compensation, and a multi-product order needs a transaction with a consistent lock order and an all-or-nothing policy. No concurrent PostgreSQL execution is claimed here.
115. How would workers claim different pending jobs without blocking behind already claimed rows?
Select a bounded set of eligible jobs with PostgreSQL FOR UPDATE SKIP LOCKED, then update those rows in the same transaction. The temporary fixture demonstrates the statement shape; real competing workers would use a shared permanent queue.
BEGIN;
CREATE TEMP TABLE jobs (
job_id integer PRIMARY KEY, status text NOT NULL, priority integer NOT NULL
);
INSERT INTO jobs VALUES (1,'pending',10),(2,'pending',5);
WITH picked AS (
SELECT job_id FROM jobs WHERE status = 'pending'
ORDER BY priority DESC, job_id
LIMIT 1 FOR UPDATE SKIP LOCKED
)
UPDATE jobs j SET status = 'running'
FROM picked p WHERE p.job_id = j.job_id
RETURNING j.job_id;
ROLLBACK
BEGIN;
CREATE TEMP TABLE jobs (
job_id integer PRIMARY KEY, status text NOT NULL, priority integer NOT NULL
);
INSERT INTO jobs VALUES (1,'pending',10),(2,'pending',5);
WITH picked AS (
SELECT job_id FROM jobs WHERE status = 'pending'
ORDER BY priority DESC, job_id
LIMIT 1 FOR UPDATE SKIP LOCKED
)
UPDATE jobs j SET status = 'running'
FROM picked p WHERE p.job_id = j.job_id
RETURNING j.job_id;
ROLLBACK
BEGIN;
CREATE TEMP TABLE jobs (
job_id integer PRIMARY KEY, status text NOT NULL, priority integer NOT NULL
);
INSERT INTO jobs VALUES (1,'pending',10),(2,'pending',5);
WITH picked AS (
SELECT job_id FROM jobs WHERE status = 'pending'
ORDER BY priority DESC, job_id
LIMIT 1 FOR UPDATE SKIP LOCKED
)
UPDATE jobs j SET status = 'running'
FROM picked p WHERE p.job_id = j.job_id
RETURNING j.job_id;
ROLLBACK
Job 1 is chosen in this isolated fixture. Skipping locked rows is appropriate for work distribution but does not provide a globally consistent analytical view or strict priority fairness. A production worker should commit its claim promptly and have a lease, recovery policy, and idempotent processing; marking a job running does not guarantee successful completion. This reviewed-only example does not simulate multiple PostgreSQL sessions.
116. How would you pair duplicate occurrences from two datasets one-to-one instead of creating a many-to-many join?
Assign an occurrence number within each business key on both sides, then join on the key and that number. Each side needs a stable unique row ID to make the pairing deterministic.
WITH left_rows(id, code) AS (VALUES (1,'A'),(2,'A'),(3,'B')),
right_rows(id, code) AS (VALUES (10,'A'),(11,'A'),(12,'A')),
l AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY code ORDER BY id) AS occurrence
FROM left_rows
), r AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY code ORDER BY id) AS occurrence
FROM right_rows
)
SELECT l.id AS left_id, r.id AS right_id, l.code
FROM l LEFT JOIN r ON r.code = l.code AND r.occurrence = l.occurrence
ORDER BY
WITH left_rows(id, code) AS (VALUES (1,'A'),(2,'A'),(3,'B')),
right_rows(id, code) AS (VALUES (10,'A'),(11,'A'),(12,'A')),
l AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY code ORDER BY id) AS occurrence
FROM left_rows
), r AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY code ORDER BY id) AS occurrence
FROM right_rows
)
SELECT l.id AS left_id, r.id AS right_id, l.code
FROM l LEFT JOIN r ON r.code = l.code AND r.occurrence = l.occurrence
ORDER BY
WITH left_rows(id, code) AS (VALUES (1,'A'),(2,'A'),(3,'B')),
right_rows(id, code) AS (VALUES (10,'A'),(11,'A'),(12,'A')),
l AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY code ORDER BY id) AS occurrence
FROM left_rows
), r AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY code ORDER BY id) AS occurrence
FROM right_rows
)
SELECT l.id AS left_id, r.id AS right_id, l.code
FROM l LEFT JOIN r ON r.code = l.code AND r.occurrence = l.occurrence
ORDER BY
Rows 1 and 2 pair with 10 and 11; row 3 remains unmatched. The extra right-side occurrence 12 is deliberately absent from this left-preserving result. Pairing by ID is a chosen allocation rule, not proof that records represent the same real event. If keys can be NULL, decide whether they form one matching group and use NULL-safe equality consistently.
117. How would you allocate issued inventory to receipts using FIFO without procedural loops?
Map receipt quantities and issue quantities onto cumulative quantity intervals; their intersection determines the allocation. This inline integer-quantity fixture assumes all receipts precede all issues, one product, and no returns. Receipt and issue IDs are the FIFO order.
WITH receipts(receipt_id, qty) AS (VALUES (1,5),(2,4)),
issues(issue_id, qty) AS (VALUES (10,3),(11,4)),
r AS (
SELECT receipt_id, SUM(qty) OVER (ORDER BY receipt_id) - qty AS lo,
SUM(qty) OVER (ORDER BY receipt_id) AS hi FROM receipts
), i AS (
SELECT issue_id, SUM(qty) OVER (ORDER BY issue_id) - qty AS lo,
SUM(qty) OVER (ORDER BY issue_id) AS hi FROM issues
)
SELECT r.receipt_id, i.issue_id,
CASE WHEN r.hi < i.hi THEN r.hi ELSE i.hi END
- CASE WHEN r.lo > i.lo THEN r.lo ELSE i.lo END AS allocated_qty
FROM r JOIN i ON r.lo < i.hi AND i.lo < r.hi
ORDER BY i.issue_id,
WITH receipts(receipt_id, qty) AS (VALUES (1,5),(2,4)),
issues(issue_id, qty) AS (VALUES (10,3),(11,4)),
r AS (
SELECT receipt_id, SUM(qty) OVER (ORDER BY receipt_id) - qty AS lo,
SUM(qty) OVER (ORDER BY receipt_id) AS hi FROM receipts
), i AS (
SELECT issue_id, SUM(qty) OVER (ORDER BY issue_id) - qty AS lo,
SUM(qty) OVER (ORDER BY issue_id) AS hi FROM issues
)
SELECT r.receipt_id, i.issue_id,
CASE WHEN r.hi < i.hi THEN r.hi ELSE i.hi END
- CASE WHEN r.lo > i.lo THEN r.lo ELSE i.lo END AS allocated_qty
FROM r JOIN i ON r.lo < i.hi AND i.lo < r.hi
ORDER BY i.issue_id,
WITH receipts(receipt_id, qty) AS (VALUES (1,5),(2,4)),
issues(issue_id, qty) AS (VALUES (10,3),(11,4)),
r AS (
SELECT receipt_id, SUM(qty) OVER (ORDER BY receipt_id) - qty AS lo,
SUM(qty) OVER (ORDER BY receipt_id) AS hi FROM receipts
), i AS (
SELECT issue_id, SUM(qty) OVER (ORDER BY issue_id) - qty AS lo,
SUM(qty) OVER (ORDER BY issue_id) AS hi FROM issues
)
SELECT r.receipt_id, i.issue_id,
CASE WHEN r.hi < i.hi THEN r.hi ELSE i.hi END
- CASE WHEN r.lo > i.lo THEN r.lo ELSE i.lo END AS allocated_qty
FROM r JOIN i ON r.lo < i.hi AND i.lo < r.hi
ORDER BY i.issue_id,
Issue 10 consumes three units from receipt 1; issue 11 consumes its remaining two and two from receipt 2. Positive quantities and unique sequence keys make the intervals well-defined. If issues exceed stock, unmatched quantity must be separately flagged rather than silently called fulfilled. Interleaved receipts and issues additionally require temporal availability checks; this deliberately scoped solution must not allocate a future receipt to an earlier issue.
118. How would you find the first inventory movement that makes a product's running balance negative?
Compute a deterministic running balance and then rank only the negative-balance rows. Assume movements(movement_id bigint primary key, product_id integer not null, occurred_at timestamptz not null, quantity_delta integer not null) includes each product's opening balance as an earlier movement.
WITH balances AS (
SELECT *, SUM(quantity_delta) OVER (
PARTITION BY product_id ORDER BY occurred_at, movement_id
ROWS UNBOUNDED PRECEDING
) AS balance
FROM movements
), negative AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY product_id ORDER BY occurred_at, movement_id
) AS negative_number
FROM balances WHERE balance < 0
)
SELECT product_id, movement_id, occurred_at, balance
FROM negative WHERE negative_number = 1 ORDER BY
WITH balances AS (
SELECT *, SUM(quantity_delta) OVER (
PARTITION BY product_id ORDER BY occurred_at, movement_id
ROWS UNBOUNDED PRECEDING
) AS balance
FROM movements
), negative AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY product_id ORDER BY occurred_at, movement_id
) AS negative_number
FROM balances WHERE balance < 0
)
SELECT product_id, movement_id, occurred_at, balance
FROM negative WHERE negative_number = 1 ORDER BY
WITH balances AS (
SELECT *, SUM(quantity_delta) OVER (
PARTITION BY product_id ORDER BY occurred_at, movement_id
ROWS UNBOUNDED PRECEDING
) AS balance
FROM movements
), negative AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY product_id ORDER BY occurred_at, movement_id
) AS negative_number
FROM balances WHERE balance < 0
)
SELECT product_id, movement_id, occurred_at, balance
FROM negative WHERE negative_number = 1 ORDER BY
Each returned product has exactly one first breach; products that never go negative are absent. Do not filter negative deltas before computing balances, because receipts must contribute too. The sequence assumes movement IDs resolve simultaneous timestamps; if simultaneous changes should be atomic, aggregate them first and change the output grain. Backdated corrections can change which historical movement is the first breach.
119. How would you find customers who buy from a category only after their first purchase from another category?
Compare category event times at customer grain, using paid orders and distinct customer-order-category combinations. This example requires the first Books purchase to occur strictly before the first Games purchase and requires both categories to exist.
WITH purchases AS (
SELECT DISTINCT o.customer_id, o.order_id, o.ordered_at, p.category
FROM orders o JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
WHERE o.status = 'paid' AND p.category IN ('Books','Games')
), firsts AS (
SELECT customer_id,
MIN(CASE WHEN category = 'Books' THEN ordered_at END) AS first_books,
MIN(CASE WHEN category = 'Games' THEN ordered_at END) AS first_games
FROM purchases GROUP BY customer_id
)
SELECT customer_id, first_books, first_games FROM firsts
WHERE first_books < first_games ORDER BY
WITH purchases AS (
SELECT DISTINCT o.customer_id, o.order_id, o.ordered_at, p.category
FROM orders o JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
WHERE o.status = 'paid' AND p.category IN ('Books','Games')
), firsts AS (
SELECT customer_id,
MIN(CASE WHEN category = 'Books' THEN ordered_at END) AS first_books,
MIN(CASE WHEN category = 'Games' THEN ordered_at END) AS first_games
FROM purchases GROUP BY customer_id
)
SELECT customer_id, first_books, first_games FROM firsts
WHERE first_books < first_games ORDER BY
WITH purchases AS (
SELECT DISTINCT o.customer_id, o.order_id, o.ordered_at, p.category
FROM orders o JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
WHERE o.status = 'paid' AND p.category IN ('Books','Games')
), firsts AS (
SELECT customer_id,
MIN(CASE WHEN category = 'Books' THEN ordered_at END) AS first_books,
MIN(CASE WHEN category = 'Games' THEN ordered_at END) AS first_games
FROM purchases GROUP BY customer_id
)
SELECT customer_id, first_books, first_games FROM firsts
WHERE first_books < first_games ORDER BY
Purchasing both categories in the same first order does not qualify. NULL comparisons naturally exclude missing categories. The rule is about first category purchase times, not whether some later game order follows some book order. Product categories are assumed historically stable; if categories change, join the category version effective at order time instead of silently using today's classification.
120. How would you reconcile a hierarchical rollup against its leaf-level source without double-counting stored subtotal rows?
Mark detail rows explicitly and aggregate only those rows into the rollup. The inline fixture contains a stored regional subtotal that must not be summed together with its children.
WITH sales(region, store, amount, row_kind) AS (
VALUES ('East','A',10,'detail'),('East','B',20,'detail'),
('East',NULL,30,'subtotal'),('West','C',5,'detail')
), computed AS (
SELECT region, SUM(amount) AS calculated
FROM sales WHERE row_kind = 'detail' GROUP BY region
), recorded AS (
SELECT region, SUM(amount) AS recorded,
COUNT(*) AS subtotal_rows
FROM sales WHERE row_kind = 'subtotal' GROUP BY region
)
SELECT c.region, c.calculated, r.recorded, COALESCE(r.subtotal_rows,0) AS subtotal_rows
FROM computed c LEFT JOIN recorded r ON r.region = c.region
WHERE r.region IS NULL OR r.subtotal_rows <> 1 OR r.recorded <> c.calculated
ORDER BY
WITH sales(region, store, amount, row_kind) AS (
VALUES ('East','A',10,'detail'),('East','B',20,'detail'),
('East',NULL,30,'subtotal'),('West','C',5,'detail')
), computed AS (
SELECT region, SUM(amount) AS calculated
FROM sales WHERE row_kind = 'detail' GROUP BY region
), recorded AS (
SELECT region, SUM(amount) AS recorded,
COUNT(*) AS subtotal_rows
FROM sales WHERE row_kind = 'subtotal' GROUP BY region
)
SELECT c.region, c.calculated, r.recorded, COALESCE(r.subtotal_rows,0) AS subtotal_rows
FROM computed c LEFT JOIN recorded r ON r.region = c.region
WHERE r.region IS NULL OR r.subtotal_rows <> 1 OR r.recorded <> c.calculated
ORDER BY
WITH sales(region, store, amount, row_kind) AS (
VALUES ('East','A',10,'detail'),('East','B',20,'detail'),
('East',NULL,30,'subtotal'),('West','C',5,'detail')
), computed AS (
SELECT region, SUM(amount) AS calculated
FROM sales WHERE row_kind = 'detail' GROUP BY region
), recorded AS (
SELECT region, SUM(amount) AS recorded,
COUNT(*) AS subtotal_rows
FROM sales WHERE row_kind = 'subtotal' GROUP BY region
)
SELECT c.region, c.calculated, r.recorded, COALESCE(r.subtotal_rows,0) AS subtotal_rows
FROM computed c LEFT JOIN recorded r ON r.region = c.region
WHERE r.region IS NULL OR r.subtotal_rows <> 1 OR r.recorded <> c.calculated
ORDER BY
Only West is reported because its subtotal is missing. A NULL store name alone is not a reliable subtotal marker when real stores may be unknown. Duplicate subtotal rows are flagged even if their sum happens to match. This detail-driven reconciliation does not report orphan subtotal-only regions; a full outer comparison is needed when those must also be audited.
SQL Coding Documentation and Further Reading
These primary PostgreSQL 16 references support the query semantics and feature assumptions used throughout the exercises. Consult the corresponding version documentation for your environment and validate engine-specific behavior with representative fixtures. Performance-sensitive queries require actual PostgreSQL plans and workload measurements; the portable-subset tests are not a substitute.
