Data Analyst Interview Questions & Answers: Set 1, E-commerce Fundamentals

SQL

1. 1.Write a query to find the second-highest order value from an orders table. How would you handle ties?

   Hide answer

   Answer: Rank the distinct order values and pick rank 2. Using DENSE_RANK() handles ties best: if two orders share the top value, the next distinct value still gets rank 2, and every order with that second-highest value is returned. Common trap: ORDER BY order_value DESC LIMIT 1 OFFSET 1 returns the top value again when the highest is tied. Also filter out NULLs, because PostgreSQL sorts NULLs first in DESC order. Table assumed: orders(order_id, order_value). For just the number, SELECT MAX(order_value) FROM orders WHERE order_value < (SELECT MAX(order_value) FROM orders) also works.

   SELECT order_id, order_value
   FROM (
     SELECT order_id, order_value,
            DENSE_RANK() OVER (ORDER BY order_value DESC) AS rnk
     FROM orders
     WHERE order_value IS NOT NULL
   ) t
   WHERE rnk = 2;

2. 2.What is the difference between WHERE and HAVING? Give an example where only HAVING works.

   Hide answer

   Answer: WHERE filters individual rows before grouping; HAVING filters groups after aggregation. So WHERE cannot use aggregates like COUNT() or SUM(), because they don't exist yet at that stage. Example where only HAVING works: find customers with more than 5 delivered orders. The status filter belongs in WHERE (row level, also faster because fewer rows get grouped), while the count condition must go in HAVING. Rule of thumb: filter rows early with WHERE, and use HAVING only for conditions on aggregated values.

   SELECT customer_id, COUNT(*) AS delivered_orders
   FROM orders
   WHERE status = 'delivered'
   GROUP BY customer_id
   HAVING COUNT(*) > 5;