TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 28

Review and Interview Prep: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

CONCEPTUAL REVIEW

  1. Q1What is the difference between WHERE and HAVING?
  2. Q2When would you choose LEFT JOIN over INNER JOIN?
  3. Q3What does GROUP BY do to rows?
  4. Q4What is the difference between COUNT(*) and COUNT(col)?
  5. Q5What does DISTINCT do? How is it different from GROUP BY?
  6. Q6What does ORDER BY without a LIMIT mean for performance?
  7. Q7What is a primary key and what does it guarantee?
  8. Q8What is a foreign key and what does it enforce?
  9. Q9What does NULL represent in a database?
  10. Q10What does COALESCE(a, b, c) return?
  11. Q11What is a CTE (WITH clause) and why use it over a subquery?
  12. Q12What does ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) produce?
  13. Q13What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?
  14. Q14What does LAG(col, 1) do inside a window function?
  15. Q15What is an index and why does it speed up queries?
  16. Q16What does EXPLAIN ANALYZE tell you that EXPLAIN alone does not?
  17. Q17What is a materialized view and how is it different from a regular view?
  18. Q18When would you use PERCENTILE_CONT vs AVG for a "central" metric?
  19. Q19What is the difference between UNION and UNION ALL?
  20. Q20What does a self-join do? Give one RetailMart use-case.
  21. Q21What is the EXISTS operator used for?
  22. Q22What is a correlated subquery?
  23. Q23What does NULLIF(a, b) return?
  24. Q24What is 1NF (First Normal Form)?
  25. Q25What does ACID stand for and why does it matter?

FOUNDATIONS DRILL

  1. Q26List the top 10 most expensive products by price.
  2. Q27Count total customers by tier (Bronze / Silver / Gold / Platinum).
  3. Q28Find all orders placed in January 2025 (order_date range).
  4. Q29Show customer first_name, last_name, email for customers who registered after 2024-06-01.
  5. Q30Count orders per order_status; sort by count descending.
  6. Q31Find the average net_total of all 'Delivered' orders.
  7. Q32Find products where price > 5000 and category is 'Electronics'.
  8. Q33List all distinct order statuses in sales.orders.
  9. Q34Find the 5 employees with the highest current_salary.
  10. Q35Count support tickets per priority; sort highest-priority first.
  11. Q36Find all products with NULL description.
  12. Q37Show the 10 stores with the most orders (join orders to stores).
  13. Q38Find customers whose phone IS NULL.
  14. Q39Count page views per device_type.
  15. Q40Show the total refund_amount from sales.returns by month.
  16. Q41Find brands with more than 50 products.
  17. Q42List all campaigns sorted by start_date ascending.
  18. Q43Count employees per department (join employees to dim_department).
  19. Q44Find products whose price < cost_price (negative margin).
  20. Q45Show total ads spend per platform from marketing.ads_spend.
  21. Q46Count call center calls per call_type.
  22. Q47List work_orders with status 'In Progress'.
  23. Q48Find all warehouses in the 'North' region.
  24. Q49Show loyalty members whose points_balance > 10000.
  25. Q50Count audit.application_logs rows per log_level.

JOINS & AGGREGATES

  1. Q51For each customer, show their name and total number of orders (LEFT JOIN so zero-order customers appear).
  2. Q52Show the top 5 products by total units sold (sum order_items.quantity).
  3. Q53Find the average order net_total per store (join orders to stores).
  4. Q54For each brand, count the number of products and their average price.
  5. Q55List customers who have placed at least one 'Returned' order.
  6. Q56Find the top 3 categories by total revenue (order_items.quantity * unit_price).
  7. Q57Show the number of support tickets per customer; list top 10.
  8. Q58Find all products that have never been ordered (LEFT JOIN anti-join pattern).
  9. Q59For each store, show the count of employees (join employees to stores).
  10. Q60Show total ads_spend per campaign from marketing.ads_spend.
  11. Q61Find customers who have reviewed at least 3 products.
  12. Q62Count shipments per carrier from sales.shipments.
  13. Q63For each category, show average product price and average cost_price.
  14. Q64Show total redemptions per loyalty tier.
  15. Q65Find employees whose salary_history shows more than one salary change.
  16. Q66Count web_events.page_views per page_url; show top 10.
  17. Q67Find the month with the highest total order revenue.
  18. Q68Show the number of returns per product category.
  19. Q69For each warehouse, count distinct products with inventory snapshots.
  20. Q70Find campaigns that generated zero ad spend.
  21. Q71Show total payment amount per payment mode (join to finance.payment_modes).
  22. Q72Count calls resolved within 5 minutes (call_duration_seconds <= 300).
  23. Q73Find the top 5 customers by total spend (sum of orders.net_total).
  24. Q74Show average review rating per product category.
  25. Q75Find all products sold by stores in the 'South' region.

WINDOW FUNCTIONS & CONDITIONALS

  1. Q76Rank customers by total spend using DENSE_RANK().
  2. Q77For each product, show its price and its rank within its category by price.
  3. Q78Compute month-over-month order count change using LAG.
  4. Q79Add a running total of net_total for each customer's orders sorted by order_date.
  5. Q80Use NTILE(4) to bucket products into price quartiles.
  6. Q81Use ROW_NUMBER to find the most recent order per customer.
  7. Q82Use FIRST_VALUE to get each employee's first salary in salary_history.
  8. Q83Classify products: CASE WHEN price > 10000 THEN 'Premium' WHEN price > 3000 THEN 'Mid' ELSE 'Budget'.
  9. Q84Add a 'tier_label' column: CASE on customers.tier for a custom label.
  10. Q85Use COALESCE to replace NULL phone with 'Not provided'.
  11. Q86Use NULLIF to turn a '0' review rating into NULL.
  12. Q87Compute a 3-row moving average of daily order count using AVG OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).
  13. Q88Show each order's net_total and the same-customer previous order's net_total (LAG).
  14. Q89For each employee, show their salary and the department average salary (AVG OVER PARTITION BY dept_id).
  15. Q90Use PERCENT_RANK() to show each product's relative price position within its brand.
  16. Q91Show SUM of net_total for the last 30 days from each order date (window frame).
  17. Q92Use CASE to categorize order status into 'Active', 'Closed', 'Problem'.
  18. Q93Find the 2nd-highest priced product per category using DENSE_RANK = 2.
  19. Q94Use LEAD to show for each call the duration of the next call by the same agent.
  20. Q95Classify customers by points_balance: CASE tiers (0-999 / 1000-4999 / 5000+).
  21. Q96Show month, total revenue, and cumulative revenue using SUM() OVER (ORDER BY month).
  22. Q97Use FILTER clause to count 'Delivered' and 'Returned' orders in the same row.
  23. Q98Find the bottom 5 employees by salary using RANK() and filtering rank <= 5.
  24. Q99Show each ticket's created_date and the time difference to the next ticket by the same customer (LEAD).
  25. Q100Write a single query that shows: customer_id, total_orders, total_spent, spending_rank (RANK), and tier label (CASE on tier).

Combined ideas, multi-step thinking

CONCEPTUAL REVIEW

  1. Q1Explain the difference between INNER JOIN and LEFT JOIN with a real scenario from RetailMart.
  2. Q2When does a LEFT JOIN produce NULL on the right side? Give a RetailMart example.
  3. Q3What is the difference between a correlated subquery and a regular subquery?
  4. Q4Why can you not use a window function directly in a WHERE clause?
  5. Q5What is the difference between ROW_NUMBER and RANK when rows tie?
  6. Q6Explain PARTITION BY vs ORDER BY inside a window function.
  7. Q7What does the FILTER clause on an aggregate do?
  8. Q8Why is PERCENTILE_CONT(0.5) more robust than AVG for skewed data?
  9. Q9What is the difference between EXISTS and IN when checking membership?
  10. Q10How do you detect the "top-N per group" pattern? What SQL structure does it use?
  11. Q11What is a lateral join and when would you use one instead of a correlated subquery?
  12. Q12What is a materialized view's main limitation compared to a regular view?
  13. Q13Why does using functions on indexed columns in WHERE break index usage?
  14. Q14What is the difference between a clustered index (PostgreSQL heap) and a BRIN index?
  15. Q15Explain the "anti-join" pattern: when to use LEFT JOIN ... IS NULL vs NOT EXISTS.
  16. Q16When would you use UNION ALL instead of UNION? What is the performance difference?
  17. Q17What is the difference between 1NF, 2NF, and 3NF? Which normal form is RetailMart?
  18. Q18What is a transaction and why does ACID matter for order processing?
  19. Q19What does EXPLAIN ANALYZE's "actual rows" vs "estimated rows" gap tell you?
  20. Q20Why does a hash join appear in EXPLAIN when the smaller table fits in memory?
  21. Q21What is a "funnel" query and what SQL constructs implement it?
  22. Q22What is an RFM score? How would you compute it from sales.orders?
  23. Q23What is cohort retention and how is it different from total active users?
  24. Q24What does IS DISTINCT FROM do that <> does not?
  25. Q25Name two common data-quality checks you would automate on customers.customers.

JOINS, CTEs & SUBQUERIES

  1. Q26The DBA needs the top 5 product categories by total revenue (quantity * unit_price from order_items joined to products). Show category_name and total_revenue.
  2. Q27Find all customers who have placed orders but have never written a review. Use an anti-join.
  3. Q28The CFO wants a year-month breakdown of total net_total, count of orders, and average net_total - but only for 'Delivered' orders.
  4. Q29Using a CTE, first compute each store's total sales, then rank stores by sales within their region. Return store_id, region, total_sales, and rank.
  5. Q30The Head of Logistics wants to see products that are in inventory (products.inventory) but have never appeared in any order_item.
  6. Q31Find the customer who spent the most in 2025 and show their name, email, and total spend.
  7. Q32Using a CTE, compute each customer's order count. Then find customers whose order count is above the overall average.
  8. Q33Find the 3 most-reviewed products per category using ROW_NUMBER in a CTE.
  9. Q34The Support Team Lead wants all customers with more than 3 open (unresolved) tickets.
  10. Q35Show each employee, their department name, and whether their salary is above or below the department average (correlated subquery or window function).
  11. Q36Find months (year-month) where total returns exceeded total revenue by more than 5%.
  12. Q37The Marketing Director wants the campaign with the highest click-through rate (email_clicks.clicked / email_clicks.sent).
  13. Q38Using LATERAL, get the last 2 orders per customer (limit to top 100 customers by spend to keep it fast).
  14. Q39Find pairs of customers who share the same shipping address (via customers.addresses - same city and address_line1).
  15. Q40Produce a "store scorecard" CTE: store_id, total_orders, total_revenue, distinct_customers, return_rate (returns / orders).
  16. Q41Using EXISTS, find products that have been purchased by at least one Gold-tier customer.
  17. Q42Find the first order date for each customer using a correlated subquery in SELECT.
  18. Q43The Compliance Officer wants all orders where the shipment was delivered before the order date (data anomaly).
  19. Q44Show each product's revenue contribution as a % of its category's total revenue.
  20. Q45Find employees who earn more than their direct manager (use stores.employees self-join via manager_id).
  21. Q46Using a recursive-free CTE, compute cumulative monthly revenue ordered by month.
  22. Q47Show brands with zero products priced above 10,000 (anti-join or HAVING).
  23. Q48The VP of Sales wants the day-of-week with the highest average net_total across all time.
  24. Q49Find customers who placed orders in both January and February 2025.
  25. Q50Show each warehouse's inventory value (SUM of quantity * price from inventory snapshots joined to products).

WINDOW FUNCTIONS & ANALYTICS

  1. Q51Compute month-over-month revenue growth % using LAG(total_revenue, 1).
  2. Q52For each customer, identify their highest-value order using FIRST_VALUE ranked by net_total DESC.
  3. Q53Compute a 7-day rolling average of daily order count.
  4. Q54Use DENSE_RANK to find the top 3 selling products per category by units sold.
  5. Q55Bucket customers into 5 equal groups by total spend using NTILE(5).
  6. Q56Compute the 90th-percentile order value per store using PERCENTILE_CONT(0.9).
  7. Q57Show for each order: net_total, same-customer previous order's net_total (LAG), and the difference.
  8. Q58Using FILTER, compute - in one query - total orders, delivered orders, and returned orders per store.
  9. Q59Pivot the order status distribution by year: columns for Delivered / Returned / Cancelled counts.
  10. Q60Compute a 30-day rolling SUM of net_total per store using ROWS BETWEEN 29 PRECEDING AND CURRENT ROW.
  11. Q61Find the product with the steepest price increase in audit.record_changes using LAG on price by product.
  12. Q62Compute each customer's "days since last order" as of the data's max date.
  13. Q63Show employees ranked by salary within their department, and flag those in the bottom 20% (NTILE).
  14. Q64Using PERCENT_RANK(), show each product's relative position by price within its brand.
  15. Q65Compute a running count of distinct customers who have placed at least one order, by month.
  16. Q66Show each page_view's view_timestamp and the gap (in seconds) to the previous view for the same customer (LAG on view_timestamp).
  17. Q67Using LEAD, show each call and whether the next call by the same agent is longer or shorter.
  18. Q68Compute the daily active users (DAU) from web_events.page_views grouped by date.
  19. Q69Compute the "stickiness" ratio: DAU / MAU per month from web_events.
  20. Q70Show average salary by department alongside the company-wide average in the same row.
  21. Q71For each product category, compute MEDIAN price (PERCENTILE_CONT) and compare to AVG.
  22. Q72Compute the quarter-over-quarter revenue growth rate using LAG(revenue, 3) on monthly data.
  23. Q73Show the top and bottom 1 product per category by average review rating (FIRST_VALUE / LAST_VALUE).
  24. Q74Add a "days to next reorder needed" column per product: days_until_reorder based on reorder_level and current quantity.
  25. Q75Compute CAGR (Compound Annual Growth Rate) of order revenue from earliest to latest year.

MIXED INTERVIEW PATTERNS

  1. Q76Write the canonical "top-N per group" query: top 3 products by revenue per category.
  2. Q77Write a dedup query using ROW_NUMBER to keep only the most-recently-registered customer per email.
  3. Q78The Finance team needs a reconciliation: for each order, compare SUM(order_items.unit_price * quantity) to orders.net_total and flag mismatches.
  4. Q79Write a funnel query: count customers who (a) viewed a page, (b) placed an order, (c) placed a second order. Show conversion % at each step.
  5. Q80Rewrite an IN subquery as EXISTS: find customers who have at least one 'Returned' order.
  6. Q81Write the median + P90 + P95 of order net_total in a single query using PERCENTILE_CONT.
  7. Q82Pivot: show for each month, total revenue split by payment mode (UPI / Card / COD / Wallet) using FILTER.
  8. Q83Simulate an EXPLAIN ANALYZE output interpretation: if Seq Scan rows = 50000 but only 10 rows match, what would you add?
  9. Q84Write a data-quality scorecard: for customers.customers, count % rows with NULL phone, % with duplicate email, % with price < cost.
  10. Q85Write a cohort retention query: for each signup_month, show retention at month 0, 1, 2, 3.
  11. Q86Build an RFM query: score each customer on Recency (days since last order), Frequency (order count), Monetary (total spend) using NTILE(5).
  12. Q87Detect slow-moving products: items in inventory > 90 days with zero orders.
  13. Q88Write a period-over-period growth table: year, month, revenue, prev_month_revenue, growth_pct.
  14. Q89Find the "champion" customers: RFM quintile 5-5-5 (best on all three dimensions).
  15. Q90Write a query that computes each store's revenue as a % of its region's total revenue.
  16. Q91Find products whose review count dropped > 20% in the last 3 months vs the prior 3 months.
  17. Q92Using a single CTE chain (no temp tables), compute: daily_orders -> monthly_totals -> YoY_growth.
  18. Q93Find customers who were active (ordered) in both 2024 and 2025.
  19. Q94Write a query to detect price anomalies: products whose price changed by > 50% in a single audit.record_changes event.
  20. Q95The DBA asks: for which columns on sales.orders would a B-tree index NOT help? Why?
  21. Q96Compute the "customer lifetime value" (CLV): average total spend per customer cohort (signup_month).
  22. Q97Flag all shipments where the delivered_date is before the shipped_date (impossible).
  23. Q98Write a UNION query combining the top 10 customers by order count and top 10 by total spend (may overlap).
  24. Q99Explain and write: why does LEFT JOIN + IS NULL = NOT EXISTS in the anti-join pattern? Show both forms.
  25. Q100The interviewer asks: "In one query, show for each category: total revenue, revenue rank, % of overall revenue, and MoM growth for the latest month." Write it.

Interview grade, edge cases

CONCEPTUAL REVIEW

  1. Q1What is the difference between RANK() and DENSE_RANK() when two rows tie at position 3?
  2. Q2Explain the "window frame" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW vs RANGE BETWEEN. When does RANGE give a different result?
  3. Q3A query uses NOT IN (subquery). What happens if the subquery returns even one NULL? Rewrite it safely.
  4. Q4What is the difference between a lateral join and a correlated subquery? When is LATERAL more efficient?
  5. Q5Why can a query with LIMIT 1 still do a full table scan? What addition fixes this?
  6. Q6Explain a hash join vs a merge join: when does PostgreSQL choose each?
  7. Q7What is a "covering index" and how would you use one for a common RetailMart query?
  8. Q8When does ANALYZE help the query planner? What does it do?
  9. Q9What is a partial index? Give a RetailMart use-case where one would dramatically reduce index size.
  10. Q10What does "seq scan" on a large table in EXPLAIN mean for the query? When is it actually fine?
  11. Q11Explain the difference between a view and a materialized view from a query-planning perspective.
  12. Q12What is a BRIN index and for which RetailMart column would you choose it over B-tree?
  13. Q13Why does GROUP BY an expression (like EXTRACT(YEAR FROM order_date)) prevent index-only scans?
  14. Q14What is the difference between PERCENTILE_CONT and PERCENTILE_DISC?
  15. Q15Explain a "funnel with conversion rate" query: how would you handle users who skip steps?
  16. Q16What is the "cohort triangle" and why are future cells always NULL?
  17. Q17What is 3NF? Name one table in RetailMart that intentionally violates it for denormalization.
  18. Q18What is optimistic locking and why might an analyst care?
  19. Q19What is a "bitmap index scan" in PostgreSQL EXPLAIN output?
  20. Q20When would you use DISTINCT ON instead of ROW_NUMBER for dedup? What are the tradeoffs?
  21. Q21Explain the performance risk of a correlated subquery in SELECT over a large dataset. How do you rewrite it?
  22. Q22What does VACUUM and AUTOVACUUM do in PostgreSQL? Why does it matter post-bulk-load?
  23. Q23What is "cardinality estimation" in a query plan and why does it affect join order?
  24. Q24Explain the difference between INTERSECT and an INNER JOIN on the same key.
  25. Q25What is a "cumulative distribution" and how does CUME_DIST() compute it?

ADVANCED JOINS, CTEs & SUBQUERIES

  1. Q26The VP of Sales asks: "Which customers bought products from at least 3 different categories in a single order?" Write the query using a CTE and GROUP BY.
  2. Q27Build a "customer activity heatmap": for each customer, count orders per day-of-week; pivot to 7 columns using FILTER.
  3. Q28Find the top 3 products by revenue per category that have also received at least 5 reviews with average rating >= 4.0.
  4. Q29Using a CTE chain, compute: (1) each store's monthly revenue, (2) each store's 3-month moving average revenue, (3) flag stores where the latest month's revenue is below their 3-month MA.
  5. Q30The Head of Logistics: "Which warehouse-product pairs have inventory below their historical average snapshot quantity?" Use a CTE with window function.
  6. Q31Find customers who placed their first order within 7 days of registration (using MIN(order_date) grouped by cust_id joined to customers.registration_date).
  7. Q32The CFO wants a "sales bridge": revenue in January 2025, then monthly deltas to December 2025, ending with a 12-month total. Use LAG and running SUM in a CTE.
  8. Q33Find all products that are in inventory at a warehouse but whose store has placed zero orders for them in the last 90 days.
  9. Q34Using a multi-step CTE: compute monthly ticket resolution rate (resolved within 48h), then identify months where this dropped more than 10% from the prior month.
  10. Q35The Marketing Director: "For each campaign, show the revenue from orders placed within 7 days of the campaign start_date." Join marketing.campaigns to sales.orders on date range.
  11. Q36Build a "customer segmentation" query: classify customers as 'New' (registered < 6 months ago), 'Active' (ordered in last 30 days), 'Lapsed' (ordered 30-180 days ago), 'Churned' (no order in 180+ days).
  12. Q37Find employees whose salary (from payroll.pay_slips) has increased every single year in salary_history (strictly monotone). Use a window function approach.
  13. Q38The Store Ops Manager: "Which stores have a higher return rate than the company-wide average?" Use two CTEs.
  14. Q39Compute the "product affinity" matrix: for each pair of product categories, how many orders contain items from both?
  15. Q40Using LATERAL, get each customer's top 3 order items by unit_price * quantity across all orders.
  16. Q41Find orders where the sum of order_items exceeds the orders.gross_total by more than 1% (reconciliation).
  17. Q42Build a multi-level CTE: (1) daily page_view counts per customer, (2) 7-day rolling distinct active customers, (3) MAU vs DAU stickiness ratio by month.
  18. Q43The Compliance Officer wants: all cases where a support ticket was opened and closed on the same day, and the customer placed a return on that same day.
  19. Q44Find "power customers": those in the top 10% by spend AND top 10% by order frequency AND who have reviewed at least one product.
  20. Q45Compute each employee's tenure in complete years (from joining_date to today) and their salary per year of tenure.
  21. Q46Using a CTE, compute the Lorenz curve data points (cumulative % of customers vs cumulative % of revenue) for the first 10 deciles.
  22. Q47Find stores that have at least one product category where they sell a product but hold zero inventory in their linked warehouse.
  23. Q48Write a query that computes, for each call center agent, their average handle time and their percentile rank among all agents.
  24. Q49Find the "cold-start" problem: customers who placed exactly 1 order (ever) and have been inactive for > 90 days.
  25. Q50Compute weekly order cohort retention for the first 4 weeks (signup_week x weeks_since_signup).

ANALYTICS & WINDOW FUNCTIONS

  1. Q51Compute a 12-month rolling revenue sum and compare each month's revenue to the 12-month rolling median.
  2. Q52Build a full RFM model: score each customer 1-5 on Recency, Frequency, and Monetary using NTILE(5), then assign a segment label (Champions / Loyal / At-Risk / Lost / New).
  3. Q53Compute the "churn rate" per month: customers who ordered in month M-1 but not in month M.
  4. Q54Show the product price history from audit.record_changes: for each price event, show the old_price, new_price, and % change using LAG.
  5. Q55Compute each store's market share (% of company revenue) by year, and its change from the prior year (LAG).
  6. Q56Use PERCENTILE_CONT(0.25) and PERCENTILE_CONT(0.75) to compute IQR for order net_total per category. Flag outliers (outside 1.5xIQR).
  7. Q57Build a "day-of-week seasonality" report: average revenue per day-of-week, with a CASE to label Mon-Sun, sorted by avg revenue.
  8. Q58Compute the "recency score" for each customer: 5 if last order within 30 days, 4 if 31-60, 3 if 61-90, 2 if 91-180, 1 if > 180 days.
  9. Q59For each product, compute the "30-day sell-through rate": units sold in last 30 days / current inventory quantity.
  10. Q60Show the top 5 employees per department by total gross pay (from pay_slips) using DENSE_RANK.
  11. Q61Compute monthly customer acquisition cost: total ads_spend / new_customers per month.
  12. Q62Build a cohort retention table: signup_month x period (0-6 months), showing % of cohort still ordering each month.
  13. Q63Using CUME_DIST(), show what % of products have a price below each product's price.
  14. Q64Compute a "basket size distribution": for each order, count distinct products; then bucket into 1 / 2-3 / 4-6 / 7+ items using CASE; show % of orders in each bucket.
  15. Q65Using a window ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING, extract the value from exactly 2 rows before (same as LAG(2, 0) but via frame).
  16. Q66Show a "day-over-day" order volume comparison for the last 30 days vs the same 30 days a year prior.
  17. Q67Compute the Gini coefficient approximation for customer spending (2 * CUME_DIST integral).
  18. Q68For each product category, show the revenue contribution at P25, Median, P75 of individual order values.
  19. Q69Build a "supply chain SLA" report: % of supply_chain.shipments delivered within 3 days of the expected delivery date.
  20. Q70Find the "revenue cliff": the month where revenue dropped the most in absolute terms (using LAG).
  21. Q71Compute each loyalty member's "tier upgrade date" - the date they first crossed the points threshold for their current tier, using cumulative SUM of points.
  22. Q72Show the "hour-of-day" call volume pattern from call_center.calls using EXTRACT(HOUR FROM call_start_time).
  23. Q73Compute a "product cannibalization" index: for pairs of products in the same category, what % of customers bought both?
  24. Q74Build a funnel: page views -> add to cart (web_events.events where event_type = 'add_to_cart') -> purchase; show conversion rates.
  25. Q75Using ROLLUP, compute revenue totals by region, by store within region, and grand total in a single query.

PERFORMANCE & DESIGN

  1. Q76The DBA reports that the query "SELECT * FROM sales.orders WHERE EXTRACT(YEAR FROM order_date) = 2025" is slow even with an index on order_date. Explain why and rewrite it to use the index.
  2. Q77Design an index strategy for the query: SELECT * FROM sales.order_items WHERE prod_id = X ORDER BY unit_price DESC LIMIT 10.
  3. Q78A query joining orders (150k rows) to order_items (375k rows) to products (6k rows) is slow. Describe what EXPLAIN ANALYZE output would reveal and what you'd fix.
  4. Q79The query SELECT * FROM customers.customers WHERE LOWER(email) = '[email protected]' won't use the email index. What index type solves this?
  5. Q80Explain why SELECT COUNT(*) FROM a large table is slow in PostgreSQL without pg_stat_user_tables. What's the fast alternative?
  6. Q81A materialized view on a 500k-row join is refreshed every hour. Describe the cost and suggest CONCURRENT REFRESH. When is it safe?
  7. Q82Write a query that benefits from a partial index: only index 'Returned' orders on sales.orders(order_date). Show the query and the index DDL.
  8. Q83The query optimizer chooses a Seq Scan instead of an index scan. Name 3 reasons why this might be the right choice.
  9. Q84Write the DDL for a composite index that covers the query: SELECT * FROM sales.order_items WHERE prod_id = X AND order_id > 1000 ORDER BY unit_price.
  10. Q85Describe the performance impact of a LATERAL join over all 50,000 customers vs bounding the outer set first.
  11. Q86A query uses ORDER BY random() LIMIT 10 to sample data. Explain the performance problem and suggest a better approach with TABLESAMPLE.
  12. Q87The query "SELECT * FROM web_events.page_views WHERE view_timestamp BETWEEN x AND y" scans a 500k-row table. What index type and any partition design would help?
  13. Q88Design a view-based analytics layer for RetailMart: what views would you create and what indexes on the base tables would support them?
  14. Q89Explain the difference between a Hash Aggregate and a Group Aggregate in EXPLAIN output. When does PostgreSQL choose each?
  15. Q90A query plan shows "Sort (cost=... rows=... width=...)" with a high cost before a Limit. How do you push the sort work down using an index?
  16. Q91Write a BRIN index DDL for web_events.page_views on view_timestamp. When would a BRIN outperform a B-tree here?
  17. Q92The Compliance Officer wants a query that runs in < 2 seconds on 500k page_views filtered by customer_id and view_timestamp range. Design the index.
  18. Q93Explain why "SELECT DISTINCT ON (cust_id) * FROM sales.orders ORDER BY cust_id, order_date DESC" requires a sort - and how an index could eliminate it.
  19. Q94Describe the dangers of force-pushing DML (UPDATE/DELETE) inside a CTE against a production table. What safeguards exist?
  20. Q95A query with 5-level CTE chain is slow. How would you identify the bottleneck CTE? (EXPLAIN ANALYZE wrapping each CTE.)
  21. Q96The query planner uses nested loops when you expect a hash join on a large table. Name the parameter that controls the planner's memory assumptions.
  22. Q97Why does adding an ORDER BY to a subquery (not the outer query) not guarantee the outer query sees rows in that order in PostgreSQL?
  23. Q98Describe the FILLFACTOR storage parameter: how would you set it on sales.orders to improve UPDATE performance?
  24. Q99Write a query that identifies "hot" pages in a table via pg_catalog.pg_stats - which columns have the highest correlation and why does that matter for index scans?
  25. Q100Design a full "analytics refresh pipeline": 3 CTEs producing a product_performance MV, a customer_segments MV, and a store_kpis MV - describe the REFRESH order and explain why order matters.

Production scenarios, optimisation

CONCEPTUAL REVIEW - STAFF ENGINEER LEVEL

  1. Q1A colleague runs NOT IN (SELECT cust_id FROM ...) and gets 0 rows unexpectedly. Walk them through exactly why NULL in the subquery causes this and write the safe alternative.
  2. Q2Explain the difference between ROWS BETWEEN and RANGE BETWEEN frames in window functions. Give a case where they produce different results on RetailMart data.
  3. Q3You need to compute a running median (not just average) by order date. Explain why window MEDIAN doesn't exist in PostgreSQL and write the workaround with PERCENTILE_CONT.
  4. Q4Describe the complete query execution lifecycle in PostgreSQL: parse -> rewrite -> plan -> execute. Where does the planner's cardinality estimate fit?
  5. Q5A LATERAL join over 50,000 customers scanning order_items 50,000 times is O(n^2). Describe two rewrite strategies to avoid this.
  6. Q6Explain what a "zombie tuple" is in PostgreSQL and why aggressive UPDATEs without VACUUM cause table bloat.
  7. Q7When should you choose a BRIN index over a B-tree? Describe the physical-layout assumption it relies on.
  8. Q8A partial index on sales.orders WHERE order_status = 'Processing' is 1/15th the size of a full index. Explain the tradeoff and when it breaks.
  9. Q9Describe the difference between an "inclusive" and "exclusive" hash join in PostgreSQL and when each appears.
  10. Q10Explain the "streaming aggregate" vs "hash aggregate" operators in EXPLAIN and when you'd prefer each.
  11. Q11What is a "ghost read" (phantom read) in REPEATABLE READ isolation and why does it still matter for analytics queries?
  12. Q12Explain MVCC (Multi-Version Concurrency Control) in two sentences. Why does it mean reads never block writes?
  13. Q13You run EXPLAIN (ANALYZE, BUFFERS) and see "Buffers: shared hit=50000 read=200". What does this tell you about cache effectiveness?
  14. Q14A materialized view refresh is causing locking on the base table. What is CONCURRENT REFRESH and what constraint does it require?
  15. Q15Describe the "index bloat" problem: when does a B-tree index grow larger than necessary and how do you reclaim space?
  16. Q16Explain the difference between INCLUDE columns in a covering index and columns in the index key. When does this matter?
  17. Q17You need to model a many-to-many relationship between customers and promotions in RetailMart. Design the junction table and explain the indexing strategy.
  18. Q18What is "table partitioning" in PostgreSQL? Which RetailMart table would benefit most from range partitioning and on which column?
  19. Q19A query planner ignores a perfectly good index on a column with 99% NULL values. Explain why and what index type helps.
  20. Q20Describe how ANALYZE updates pg_statistic. Why does a freshly loaded table sometimes have terrible query plans?
  21. Q21Explain the difference between a "correlated subquery" and a "decorrelated subquery" in PostgreSQL's planner. How does the planner transform one to the other?
  22. Q22What is a "merge join" in PostgreSQL? What precondition must both inputs satisfy and what is the amortized cost?
  23. Q23You need to deduplicate a 50M-row table while keeping the latest row per business key. Describe the safest production approach using a CTE and DELETE.
  24. Q24Explain the tradeoff between a view and a CTE for code reuse in PostgreSQL. When does a CTE become a "CTE fence"?
  25. Q25Describe a "write-heavy" column (like order_status) and explain why indexing it may hurt write throughput more than it helps reads.

END-TO-END ANALYTICS PIPELINES

  1. Q26Write a single CTE chain that computes the complete customer lifecycle dashboard: total_orders, total_spend, avg_order_value, days_since_last_order, loyalty_tier, RFM_score (1-5 composite), and churn_risk_flag (> 180 days inactive).
  2. Q27Build a multi-step revenue attribution query: for each campaign in marketing.campaigns, compute the revenue from orders placed within 7 days of campaign launch (by the same customer who received an email_click).
  3. Q28Write a "supply chain health" pipeline CTE: warehouse inventory coverage days (inventory / avg daily orders), SLA breach rate (shipments delivered late), and dead-stock flag (products with 0 orders in 90 days).
  4. Q29Build a full cohort retention matrix using a CTE: signup_month x period_index (0-6), with cohort_size, retained_customers, and retention_pct. Return the result as a "long" table (not pivoted).
  5. Q30Write a query that computes the complete product P&L: revenue (quantity * unit_price), cost (quantity * cost_price), gross_margin, and return_rate (returns / orders) per product, along with a running cumulative revenue rank (ABC analysis).
  6. Q31Build a "store ops scorecard" CTE: for each store, compute (1) total revenue, (2) avg ticket resolution time (from support.tickets where resolved_date IS NOT NULL), (3) return rate, (4) top-selling category, (5) employee count - all in one CTE chain.
  7. Q32Write a funnel query across three systems: web_events.page_views (visit) -> web_events.events where event_type = 'add_to_cart' -> sales.orders (purchase). Show customer counts and conversion rates at each step.
  8. Q33Build a "wallet reconciliation" report: for each customer, compare customers.wallets.balance to the net of loyalty.redemptions and sales.payments credited, and flag discrepancies > Rs100.
  9. Q34Write a "payroll audit" CTE: for each employee, compute their expected gross pay from payroll.pay_slips, compare to hr.salary_history, and flag any month where the two differ by > 5%.
  10. Q35Build a complete "email marketing effectiveness" report: per campaign, show send count, open rate, click rate, orders placed (within 7 days), revenue, and revenue-per-email-sent - all in one CTE chain.
  11. Q36Write a "product returns root-cause" query: join sales.returns to support.tickets on the same order_id, and for each return reason (or NULL if no ticket), compute volume, avg refund_amount, and % of total returns.
  12. Q37Build an "analyst-grade DAU/MAU/WAU" dashboard from web_events.page_views: compute per month the daily active users (avg per day), weekly active users (avg per week), and stickiness ratios (DAU/MAU, WAU/MAU).
  13. Q38Write a "new vs returning revenue" split: for each month, compute revenue from customers placing their first-ever order vs revenue from repeat customers. Use a CTE that identifies each customer's first order date.
  14. Q39Build a "fraud-signal" query: flag orders where (a) gross_total > 3x the customer's historical avg order, AND (b) the order was placed within 24 hours of an address change (if available), AND (c) the order status is 'Processing'.
  15. Q40Write a comprehensive "inventory health" report: per product per warehouse, compute current_qty, avg_daily_sales (30-day), days_of_stock, reorder_urgency (CASE: 'Critical' < 7 days / 'Low' 7-30 / 'OK' > 30), and value_at_risk (qty * cost_price).
  16. Q41Build a "HR cost analysis" pipeline: department, total headcount, total annual payroll (12x avg net_salary), payroll as % of total company payroll, and a year-over-year payroll growth rate (using LAG on salary_history).
  17. Q42Write an "audit trail completeness" query: for each table schema, count rows in audit.record_changes and check if the ratio of change events to base table rows is above a minimum threshold (suggesting proper auditing).
  18. Q43Build a "customer support SLA" dashboard: per ticket_priority, compute (1) avg resolution hours, (2) % resolved within SLA (Critical < 4h, High < 24h, Medium < 72h, Low < 168h), (3) breach trend vs prior month.
  19. Q44Write a "cross-sell opportunity" query: for each product category pair (A, B), compute the number of customers who bought A but never bought B - ranked by opportunity size.
  20. Q45Build a "brand health" index: per brand, compute avg review rating, review_count, avg price, revenue share of its category, return rate, and a composite health_score (weighted formula of your design).
  21. Q46Write a "peak load" analysis: from web_events.page_views and call_center.calls, identify the hours of day and days of week where both page view volume and call volume are simultaneously highest - flag the top 5 combined load windows.
  22. Q47Build a "regional manager scorecard" CTE: for each regional manager (employee with role = 'Regional Manager'), compute total revenue across all their stores, avg store return rate, avg employee satisfaction (from hr.attendance punctuality proxy), and headcount.
  23. Q48Write a "product lifecycle" classification: for each product, using audit.record_changes price events and order_items sales data, classify as 'Growing' (increasing revenue trend), 'Mature' (stable), 'Declining' (decreasing trend) using LAG-based month-over-month changes.
  24. Q49Build a "loyalty program effectiveness" pipeline: per loyalty tier, compute (a) avg points_balance, (b) avg order frequency, (c) avg total spend, (d) redemption rate, (e) vs customers NOT in loyalty program - show the delta for each metric.
  25. Q50Write a single CTE-chain that produces the "executive weekly briefing" summary: this_week_revenue, last_week_revenue, wow_growth_pct, mtd_revenue, ytd_revenue, new_customers_this_week, top_category_this_week, top_store_this_week - all in one row.

PRODUCTION ANALYTICS PATTERNS

  1. Q51You need a "slowly changing dimension" view for customer tier: show each customer's tier, the date they moved to that tier, and the date they left (NULL if current). Use audit.record_changes where field_name = 'tier'.
  2. Q52Implement a "late-arriving fact" detection query: orders inserted in the last 24 hours with an order_date > 30 days in the past (late data loader). Flag them and compute how they affect monthly revenue totals.
  3. Q53Build a "data completeness matrix": for each schema.table, compute the % of columns with > 1% NULLs, % of rows with at least one NULL, and a "completeness_score" (100 = fully populated).
  4. Q54Write a "price elasticity" approximation: for each product, correlate price changes (from audit.record_changes) to subsequent order volume changes. Use LAG to get pre/post price and volume.
  5. Q55Build a "support agent efficiency" query: per call center agent, compute (1) avg call duration, (2) calls per shift (8h window), (3) % calls with a matching support ticket resolved same day, (4) overall efficiency_score.
  6. Q56Implement a "B-segment" store identification: stores ranked 11-30 by revenue (not top 10, not bottom), with their revenue, growth rate vs prior period, and the revenue gap to the top-10 cutoff.
  7. Q57Write a "demand forecasting input" query: for each product x month, produce: units_sold, avg_daily_sold, trailing_3m_avg, trailing_12m_avg, yoy_growth, and a naive_forecast (trailing_3m_avg projected for next month).
  8. Q58Build a "return rate anomaly detector": using a z-score approach, flag products whose return_rate is > 2 standard deviations above the category mean.
  9. Q59Implement a "multi-touch attribution" model: for each order, identify all marketing.email_clicks within 30 days prior; distribute revenue equally across campaigns (even-touch attribution).
  10. Q60Write a "geographic concentration risk" query: compute the Herfindahl-Hirschman Index (HHI) of revenue by store_city - SUM of (city_revenue_share)^2 - to measure geographic concentration.
  11. Q61Build a "customer-to-customer cohort flow" query: show how many customers moved from one tier (Bronze/Silver/Gold/Platinum) to another between two consecutive years (using tier_updated_at).
  12. Q62Write a "supply chain bullwhip" indicator: for each product, compare the coefficient of variation (CV = stddev/avg) of end-customer demand (from order_items) vs warehouse supply_chain.shipment quantities per week.
  13. Q63Implement a "real-time order health" CTE: for each order_id in the last 24 hours, join to shipments, payments, and order_items - flag any order where one of these is missing (orphan detection).
  14. Q64Build a "customer 360" profile query: for a given customer_id, show in a single result set: personal info, loyalty tier, RFM score, last 3 orders, open tickets, recent web events, and total lifetime value.
  15. Q65Write a "markdown impact" analysis: from promotions joined to order_items, compute revenue with vs without promotions active, and the lift (%) per product category per promotion period.
  16. Q66Build an "employee attrition risk" proxy: compute per employee the % of days absent (from hr.attendance), salary percentile within department, and salary_growth_rate - flag those with high absence + low salary growth as "at-risk".
  17. Q67Implement a "product recommendation engine input": compute a customer x category affinity matrix - for each customer x category pair, compute purchase_frequency and avg_spend - the top 3 categories not yet purchased are recommendations.
  18. Q68Write a "flash sale impact" query: identify 24-hour windows where order volume > 3x the daily average, and for those windows compute incremental revenue, return rate spike, and support ticket surge.
  19. Q69Build a "supplier reliability" scorecard: per supplier, compute on-time delivery rate, defect rate (returns as % of order items), lead time variability (STDDEV of days from supply_chain.shipments), and a reliability_score.
  20. Q70Write a "revenue leakage" detector: find all orders where payment_mode was COD (cash on delivery) but finance.payments shows no corresponding payment record - these are unfulfilled COD collections.
  21. Q71Implement a "dead-stock recovery" ranking: products in warehouse inventory with 0 orders in 120 days, ranked by (current_qty x cost_price) descending - the capital tied up in dead stock.
  22. Q72Build a "promotional ROI" model: per campaign, compute total ads_spend, revenue attributable (orders within 7 days), ROI = (revenue - spend) / spend, and payback_days (spend / daily_revenue_lift).
  23. Q73Write a "support ticket sentiment proxy" query: since we lack NLP, use ticket subject length > 100 chars as a proxy for "detailed/frustrated" complaints. Compute % of such tickets per product category.
  24. Q74Implement a "customer win-back" target list: customers who spent > Rs10,000 total in 2024 but placed 0 orders in 2025, ranked by their 2024 total spend descending.
  25. Q75Build a "call center IVR bypass" analysis: calls with call_duration_seconds < 30 are likely abandoned. Compute abandoned rate by hour-of-day and identify the top 3 hours for staffing gaps.

ARCHITECTURE & SYSTEM DESIGN

  1. Q76Design the analytics schema for RetailMart's BI layer: which base-table queries would you materialize as MVs, what refresh cadence, and what indexes on the base tables would support fast incremental reads?
  2. Q77The data team wants to build a "self-serve analytics" layer on RetailMart. Design a view hierarchy: raw -> cleansed -> aggregated -> business-metric views. Name 3 views per layer and their dependencies.
  3. Q78A data engineer proposes partitioning sales.orders by order_date (monthly range). Describe the benefits, risks, and what changes to existing queries.
  4. Q79The ops team wants to archive orders older than 3 years to a cold table. Describe the migration strategy using a CTE with a writable CTE (DELETE ... RETURNING) - and why this is done in batches.
  5. Q80Describe a "materialized view refresh strategy" for RetailMart that minimizes lock time: which MVs can use CONCURRENT REFRESH, which cannot (due to no unique index), and what alternative is there for those?
  6. Q81Design an "event-driven" pipeline to keep a customer_rfm_scores table up to date: what triggers an RFM recompute (new order, new return), how would you implement this with PostgreSQL triggers + a refresh_queue?
  7. Q82The marketing team needs a campaign attribution table populated nightly. Describe the SQL pipeline (CTEs + INSERT INTO ... SELECT) and what idempotency mechanism prevents double-counting.
  8. Q83Design a "data contract" for the sales.orders table: list the constraints (CHECK, FK, NOT NULL, UNIQUE) you would add to guarantee data quality without touching existing rows.
  9. Q84You discover that the page_views table (500k rows, growing 10k/day) is causing the daily analytics job to take 45 minutes. Propose a partitioning + BRIN + parallel-query solution.
  10. Q85A query joining 6 tables takes 30 seconds. Describe a systematic EXPLAIN ANALYZE-driven diagnosis: what operators would you look for, what statistics would you check, and what changes would you make?
  11. Q86Design a "slowly changing dimension" (SCD Type 2) implementation for customers.customers.tier: what columns would you add, how would you populate valid_from / valid_to, and how would you query "tier as of a given date"?
  12. Q87The CFO wants a "financial close" report that reconciles sales.orders, finance.payments, and finance.accounts every month-end. Design the SQL reconciliation query and describe what happens if discrepancies are found.
  13. Q88Design an indexing strategy for the RetailMart analytics workload: list 5 specific indexes (table, columns, type, partial condition) that would have the highest impact on the most-run queries.
  14. Q89Describe the tradeoffs between storing pre-aggregated monthly_revenue in a table vs computing it on demand from sales.orders each time. When does pre-aggregation break?
  15. Q90A new requirement: track which analyst ran which query and when, for audit compliance. Describe how you'd use audit.api_requests or pg_stat_statements to build this, without installing extensions.
  16. Q91The student database (practice) needs to be reset between cohorts. Design a "reset script" that drops all student-created objects (tables, views, indexes in public schema) but preserves the RetailMart schemas.
  17. Q92Describe the "write amplification" problem when adding multiple indexes to sales.order_items (374k rows, heavy insert load). Quantify the tradeoff and state which 2 indexes you would keep.
  18. Q93The HR team wants to run ad-hoc salary analysis but must not see individual employee names or IDs (PII). Design a row-level security policy or a view-based masking approach in PostgreSQL.
  19. Q94Design a "canary query" - a simple SQL statement you'd run each morning to validate that last night's data load into RetailMart V3 completed successfully (counts, date ranges, FK checks).
  20. Q95The support team reports that queries against support.tickets are slow after 12 months of data growth. Describe your full investigation: pg_stat_user_indexes -> EXPLAIN ANALYZE -> index creation -> VACUUM ANALYZE.
  21. Q96Design a "multi-tenant" extension of RetailMart where each tenant has its own schema but shares the same PostgreSQL instance. What isolation issues arise, and how does search_path help?
  22. Q97Describe a "blue-green" deployment approach for a major schema change on sales.orders (adding a new column with a NOT NULL default). How do you avoid a long lock?
  23. Q98The analytics team wants column-level encryption on customers.email for GDPR compliance. Describe the pgcrypto approach vs the application-level approach and the query impact of each.
  24. Q99Design a "query budget" system: each analyst is allowed to run queries that scan at most 10M rows per day. How would you implement this using pg_stat_statements + a daily quota table?
  25. Q100Write your personal "RetailMart mastery checklist": 10 SQL patterns from this course that you could explain in an interview in under 2 minutes each - and for each, name the RetailMart query that best demonstrates it.