Product's Price for Each Store

Product's Price for Each Store

What is this problem about?

The Product's Price for Each Store SQL problem asks you to pivot a Products table (product_id, store, price) into a wide format with one column per store. This easy SQL problem tests conditional aggregation (CASE WHEN) for pivot operations. The database interview pattern is demonstrated.

Why is this asked in interviews?

Amazon and McAfee ask this to test SQL pivoting — converting rows to columns. This is a fundamental data transformation skill in analytics and reporting.

Algorithmic pattern used

Conditional aggregation (pivot). SELECT product_id, MAX(CASE WHEN store='store1' THEN price END) AS store1, MAX(CASE WHEN store='store2' THEN price END) AS store2, MAX(CASE WHEN store='store3' THEN price END) AS store3 FROM Products GROUP BY product_id.

Example explanation

Products: (1,store1,95),(1,store2,70),(1,store3,105),(2,store1,70),(2,store3,80). Pivoted: prod1→(95,70,105), prod2→(70,NULL,80). Result: [(1,95,70,105),(2,70,NULL,80)].

Common mistakes candidates make

• Using SUM instead of MAX for CASE WHEN (both work here but MAX is cleaner).
• Not grouping by product_id.
• Missing NULL for stores where a product isn't sold.
• Hardcoding wrong store names.

Interview preparation tip

SQL pivoting uses CASE WHEN inside aggregate functions. The pattern: MAX(CASE WHEN category='X' THEN value END) AS X. Always GROUP BY the row identifier. For dynamic pivots (unknown number of stores), use dynamic SQL or Python/pandas. Practice pivoting for: "revenue by month," "scores by subject," "count by category." Conditional aggregation is the universal SQL pivot technique.