Data Analyst Interview Questions & Answers: Set 20, Final Mock Interview

SQL

1. 1.Write a query to find users whose spending increased every month for 3 months in a row.

   Hide answer

   Answer: Aggregate spend to one row per user per month, then use LAG to compare each month with the previous two. A user qualifies when month 3 > month 2 > month 1 and the three months are consecutive. Tables assumed: orders(user_id, order_date, amount). Trap: a month with no orders has no row, so LAG could silently skip a gap. The check on mon2 makes sure the months really are back to back.

   WITH m AS (
     SELECT user_id, DATE_TRUNC('month', order_date) AS mon,
            SUM(amount) AS spend
     FROM orders
     GROUP BY 1, 2
   ), l AS (
     SELECT user_id, mon, spend,
            LAG(spend, 1) OVER w AS p1,
            LAG(spend, 2) OVER w AS p2,
            LAG(mon, 2)   OVER w AS mon2
     FROM m
     WINDOW w AS (PARTITION BY user_id ORDER BY mon)
   )
   SELECT DISTINCT user_id
   FROM l
   WHERE spend > p1 AND p1 > p2
     AND mon2 = mon - INTERVAL '2 months';

2. 2.Calculate the median order value in SQL without a built-in MEDIAN function.

   Hide answer

   Answer: Number the rows in order of value and count the total. Then average the middle row, or the two middle rows when the count is even. With integer division, (cnt+1)/2 and (cnt+2)/2 give the same row when cnt is odd and the two middle rows when it is even. Table assumed: orders(order_id, amount). In PostgreSQL you can also use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount), but interviewers usually want the manual method.

   WITH r AS (
     SELECT amount,
            ROW_NUMBER() OVER (ORDER BY amount) AS rn,
            COUNT(*) OVER ()                    AS cnt
     FROM orders
   )
   SELECT AVG(amount) AS median_order_value
   FROM r
   WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2);