Sales Analysis III - Problem

You have two tables: Product and Sales.

The Product table contains information about products with their ID, name, and unit price. The Sales table records all sales transactions with seller ID, product ID, buyer ID, sale date, quantity, and price.

Write a SQL query to find products that were only sold in the first quarter of 2019 (between 2019-01-01 and 2019-03-31 inclusive). This means:

  • The product must have at least one sale in Q1 2019
  • The product must have no sales outside of Q1 2019

Return the result in any order.

Table Schema

Product
Column Name Type Description
product_id PK int Primary key, unique product identifier
product_name varchar Name of the product
unit_price int Price per unit of the product
Primary Key: product_id
Sales
Column Name Type Description
seller_id int ID of the seller
product_id int Foreign key referencing Product table
buyer_id int ID of the buyer
sale_date date Date when the sale occurred
quantity int Quantity of products sold
price int Total price of the sale

Input & Output

Example 1 — Mixed Quarter Sales
Input Tables:
Product
product_id product_name unit_price
1 S8 1000
2 G4 800
3 iPhone 1400
Sales
seller_id product_id buyer_id sale_date quantity price
1 1 1 2019-01-21 2 2000
1 2 2 2019-02-17 1 800
2 2 3 2019-06-02 1 800
3 3 3 2019-05-13 2 2800
Output:
product_id product_name unit_price
1 S8 1000
💡 Note:

Product 1 (S8) was only sold on 2019-01-21, which is within Q1 2019. Product 2 (G4) was sold in both February (Q1) and June (Q2), so it doesn't qualify. Product 3 (iPhone) was only sold in May (Q2), so it doesn't qualify either.
Example 2 — No Q1 2019 Sales
Input Tables:
Product
product_id product_name unit_price
1 Laptop 1200
2 Mouse 25
Sales
seller_id product_id buyer_id sale_date quantity price
1 1 1 2018-12-25 1 1200
2 2 2 2019-04-15 3 75
Output:
product_id product_name unit_price
💡 Note:

No products qualify because Product 1 was sold in December 2018 (before Q1 2019) and Product 2 was sold in April 2019 (after Q1 2019). Neither product has any sales within the first quarter of 2019.
Example 3 — Multiple Q1 Sales Only
Input Tables:
Product
product_id product_name unit_price
1 Tablet 600
2 Keyboard 100
Sales
seller_id product_id buyer_id sale_date quantity price
1 1 1 2019-01-01 1 600
1 1 2 2019-03-31 2 1200
2 2 3 2019-02-15 1 100
Output:
product_id product_name unit_price
1 Tablet 600
2 Keyboard 100
💡 Note:

Both products qualify. Product 1 (Tablet) was sold twice in Q1 2019 (January 1st and March 31st, the boundary dates). Product 2 (Keyboard) was sold once in February 2019. Neither product has any sales outside Q1 2019.

Constraints

  • 1 ≤ product_id ≤ 1000
  • product_name consists of lowercase English letters, digits, and spaces
  • 1 ≤ unit_price ≤ 1000
  • sale_date is a valid date
  • 1 ≤ quantity ≤ 100
  • price = quantity × unit_price

Visualization

Tap to expand
Sales Analysis III: Find Q1 2019 Exclusive ProductsInput: Combined Sales Dataproduct_idnamesale_date1S82019-01-212G42019-02-172G42019-06-02Product 1: Only Q1 sales ✓Product 2: Q1 + Q2 sales ✗GROUP BYproduct_id+ HAVING filterOutput: Q1 2019 Only Productsproduct_idproduct_nameunit_price1S81000HAVING Condition:COUNT(Q1 sales) > 0 ANDCOUNT(non-Q1 sales) = 0✓ Only products with exclusive Q1 2019 sales are returned
Understanding the Visualization
1
Join Tables
Combine Product and Sales on product_id
2
Group Products
Group by product to analyze all sales per product
3
Filter Period
Use HAVING with conditional COUNT for Q1-only sales
Key Takeaway
🎯 Key Insight: Use conditional COUNT aggregation in HAVING clause to filter products based on date ranges

Related Problems

Asked in
Amazon 12 Microsoft 8 Google 6
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen