Sales Analysis III

Sales Analysis III

What is this problem about?

The Sales Analysis III interview question asks you to find all products that were sold ONLY in the first quarter of 2019 (January 1 to March 31, 2019). A product qualifies if every sale record for that product falls within this date range — products also sold outside this range must be excluded. This is a "group filter" SQL problem requiring per-product date boundary checking.

Why is this asked in interviews?

Amazon and Bloomberg ask this SQL problem because it tests per-group filtering using HAVING with date aggregates — a pattern essential in business analytics for cohort analysis, seasonal reporting, and date-range compliance checks. It also distinguishes candidates who understand that filtering must happen at the group level (HAVING), not the row level (WHERE).

Algorithmic pattern used

The pattern is GROUP BY with HAVING on date aggregates. Group sales by product. For each product, check that the minimum sale date is ≥ '2019-01-01' AND the maximum sale date is ≤ '2019-03-31'. This ensures every sale for that product falls within Q1 2019.

SELECT s.product_id, p.product_name
FROM Sales s
JOIN Product p ON s.product_id = p.product_id
GROUP BY s.product_id, p.product_name
HAVING MIN(s.sale_date) >= '2019-01-01'
   AND MAX(s.sale_date) <= '2019-03-31';

Example explanation

Sales:

• Product 1: sold on 2019-01-10, 2019-02-15 → both in Q1 2019 → qualifies.
• Product 2: sold on 2019-01-05, 2019-04-01 → April sale is outside Q1 → excluded.
• Product 3: sold on 2018-12-20 → before Q1 2019 → excluded.

Result: Product 1 only.

MIN/MAX approach: Product 1 MIN=Jan 10, MAX=Feb 15 — both within bounds → include. Product 2 MAX=Apr 1 > Mar 31 → exclude.

Common mistakes candidates make

• Using WHERE to filter dates — this filters individual rows, not ensuring all rows for a product are in range.
• Using sale_date BETWEEN '2019-01-01' AND '2019-03-31' in WHERE — excludes some rows but doesn't prevent products with other-date sales from appearing.
• Not joining with the Product table when product_name is required in the output.
• Using OR instead of AND in the HAVING clause — must satisfy both conditions simultaneously.

Interview preparation tip

For the Sales Analysis III coding problem, the database interview pattern is GROUP BY with HAVING on MIN/MAX aggregates for date-range compliance. The key insight: checking MIN ≥ start AND MAX ≤ end is equivalent to checking ALL dates are in range. Bloomberg interviewers may ask "can you use NOT EXISTS instead?" — yes, but the HAVING approach is more concise. Practice writing HAVING clauses with date arithmetic for seasonal business analytics queries.