Write SQL to verify quarterly sales decline
Company: Fetch Rewards
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given a single dataset for a beverage company and must decide whether Q2 2024 truly declined. Write one standard SQL query that produces a comparison table with overall and same-store metrics, guarding against grain mistakes and misleading averages.
Schema:
- transactions(transaction_id BIGINT, sale_date DATE, store_id INT, product_id INT, units INT, revenue DECIMAL(10,2), is_return BOOLEAN)
- stores(store_id INT, region VARCHAR, opened_date DATE, closed_date DATE NULL)
- products(product_id INT, category VARCHAR)
Sample data (minimal, illustrative):
transactions
+---------------+------------+----------+------------+-------+---------+-----------+
| transaction_id| sale_date | store_id | product_id | units | revenue | is_return |
+---------------+------------+----------+------------+-------+---------+-----------+
| 1 | 2024-03-30 | 101 | 11 | 10 | 50.00 | false |
| 2 | 2024-04-02 | 101 | 11 | 8 | 44.00 | false |
| 3 | 2024-04-15 | 102 | 12 | 5 | 40.00 | false |
| 4 | 2024-05-01 | 103 | 11 | -1 | -5.50 | true |
| 5 | 2024-06-20 | 101 | 12 | 7 | 63.00 | false |
| 6 | 2023-06-10 | 101 | 11 | 9 | 45.00 | false |
+---------------+------------+----------+------------+-------+---------+-----------+
stores
+---------+--------+-------------+-------------+
| store_id| region | opened_date | closed_date |
+---------+--------+-------------+-------------+
| 101 | West | 2020-01-01 | NULL |
| 102 | East | 2021-05-10 | NULL |
| 103 | North | 2024-05-01 | 2024-07-15 |
+---------+--------+-------------+-------------+
products
+------------+----------+
| product_id | category |
+------------+----------+
| 11 | Soda |
| 12 | Juice |
+------------+----------+
Requirements:
1) Define quarters as: Q1 2024 = 2024-01-01..2024-03-31; Q2 2024 = 2024-04-01..2024-06-30; Q2 2023 = 2023-04-01..2023-06-30.
2) Produce two comparison rows: 'Q2_2024_vs_Q1_2024' and 'Q2_2024_vs_Q2_2023'.
3) For each comparison, output: total_revenue_period_a, total_revenue_period_b, revenue_pct_change, total_units_period_a, total_units_period_b, units_pct_change, avg_price_period_a (sum(revenue)/nullif(sum(units),0)), avg_price_period_b, same_store_revenue_pct_change (restrict stores open for the entirety of both periods: opened_date <= period_start AND (closed_date IS NULL OR closed_date >= period_end)), active_store_count_a, active_store_count_b, decline_flag (true if revenue_pct_change < -5% AND units_pct_change < -5%).
4) Treat returns as negative amounts (no special filtering). Avoid averaging daily averages; compute period-level sums first, then ratios. Ensure correct grain (day x store x product) so that joins do not duplicate rows.
5) The query should be ANSI SQL and run without UDFs; use CTEs and window functions if helpful.
Overview: This question evaluates proficiency in SQL aggregation, time-series period-over-period comparison, same-store cohort analysis, correct grain handling, and treatment of returns, testing data manipulation and analytical SQL skills in the Data Manipulation (SQL/Python) domain.
Read the full Fetch Rewards Data Scientist interview experience this question came from
You work for a beverage company and are given a transactional dataset. Your task is to decide whether Q2 2024 truly declined by building a comparison table between quarters.
Write ONE ANSI SQL query (no UDFs) that produces a summary table comparing Q2 2024 with:
- Q1 2024, and
- Q2 2023
Use the following schema:
- transactions(transaction_id BIGINT, sale_date DATE, store_id INT, product_id INT, units INT, revenue DECIMAL(10,2), is_return BOOLEAN)
- stores(store_id INT, region VARCHAR(50), opened_date DATE, closed_date DATE NULL)
- products(product_id INT, category VARCHAR(50))
Quarters are defined as:
- Q1 2024 = 2024-01-01 to 2024-03-31 (inclusive)
- Q2 2024 = 2024-04-01 to 2024-06-30 (inclusive)
- Q2 2023 = 2023-04-01 to 2023-06-30 (inclusive)
The query must output exactly two rows, with comparison labels:
- 'Q2_2024_vs_Q1_2024'
- 'Q2_2024_vs_Q2_2023'
For each row (comparison), treat Q2 2024 as **period A** and the other quarter as **period B**. Output the following columns:
- comparison_label (one of the two strings above)
- total_revenue_period_a (sum of revenue in Q2 2024, returns treated as negative)
- total_revenue_period_b (sum of revenue in comparison quarter, returns treated as negative)
- revenue_pct_change = (total_revenue_period_a - total_revenue_period_b) / NULLIF(total_revenue_period_b, 0) * 100
- total_units_period_a (sum of units in Q2 2024, returns treated as negative)
- total_units_period_b (sum of units in comparison quarter, returns treated as negative)
- units_pct_change = (total_units_period_a - total_units_period_b) / NULLIF(total_units_period_b, 0) * 100
- avg_price_period_a = sum(revenue) / NULLIF(sum(units), 0) for Q2 2024
- avg_price_period_b = sum(revenue) / NULLIF(sum(units), 0) for the comparison quarter
- same_store_revenue_pct_change: revenue percent change restricted to "same-store" locations only, where a store is a same-store for a given comparison if it was open for the entire combined span of both periods:
- opened_date <= comparison_combined_start AND (closed_date IS NULL OR closed_date >= comparison_combined_end)
- For 'Q2_2024_vs_Q1_2024': combined_start = '2024-01-01', combined_end = '2024-06-30'
- For 'Q2_2024_vs_Q2_2023': combined_start = '2023-04-01', combined_end = '2024-06-30'
- Compute same_store_revenue_pct_change as (same_store_revenue_period_a - same_store_revenue_period_b) / NULLIF(same_store_revenue_period_b, 0) * 100
- active_store_count_period_a: number of stores that were open at any time during period A (opened_date <= period_end AND (closed_date IS NULL OR closed_date >= period_start))
- active_store_count_period_b: number of stores that were open at any time during period B (same condition, using that period's dates)
- decline_flag: TRUE if revenue_pct_change < -5 AND units_pct_change < -5, otherwise FALSE
Additional requirements:
- Treat returns as negative amounts; do not filter them out. (The sample data already uses negative units and revenue for returns.)
- Avoid misleading averages: always aggregate to the period (quarter) level first (sums), then compute ratios/percentages from those aggregates.
- Ensure correct grain so that joins do not duplicate transaction rows. You may use CTEs and window functions.
Write a single standard SQL query that produces the required comparison table with the columns above.
Tables
transactions(transaction_id BIGINT, sale_date DATE, store_id INT, product_id INT, units INT, revenue DECIMAL(10,2), is_return BOOLEAN)
stores(store_id INT, region VARCHAR(50), opened_date DATE, closed_date DATE)
products(product_id INT, category VARCHAR(50))
Hints
- Define the quarters in a small CTE and aggregate transactions to a store x quarter grain before computing any ratios.
- Use a comparison mapping CTE (with period_a, period_b, combined_start, combined_end) to drive both same-store filtering and the final two output rows.