Coding questions · Amazon
Compute unique visitors by department
This question evaluates proficiency in SQL data manipulation, specifically JOIN operations between event and product tables, deduplicating distinct users, applying time-range filters, and reasoning about hierarchical product attributes (department/category/subcategory).
You have two tables: products(product_id, department, category, subcategory) where department > category > subcategory form a parent–child hierarchy, and click_log(user_id, product_id, event_time). For any given department name as input, write SQL to return the number of distinct users who clicked products in that department over a specified time range. Ensure that a user who clicks multiple products within the same department is counted once, and extend your query to return counts for all departments in one result.
Compute unique visitors by department over a date range
You have two tables:
- products(product_id, department, category, subcategory) where department > category > subcategory form a parent–child hierarchy.
- click_log(user_id, product_id, event_time).
For the fixed date range FROM 2025-05-01 TO 2025-05-31 (inclusive), write SQL to return, for each department, the number of distinct users who clicked products in that department.
A user who clicks multiple products within the same department must be counted only once for that department in the date range. A user who clicks products in multiple departments should be counted once in each of those departments. The final result should have one row per department with its distinct user count for that period.
(Assume that in a real system, this query could be filtered for a single department by adding a WHERE clause on department, but your answer here should produce counts for all departments.)
Table products
| column | type | constraints |
|---|---|---|
| product_id | INT | PRIMARY KEY, NOT NULL |
| department | VARCHAR(50) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| subcategory | VARCHAR(50) | NOT NULL |
Sample data:
| category | department | product_id | subcategory |
|---|---|---|---|
| Phones | Electronics | 1 | Smartphones |
| Phones | Electronics | 2 | Feature Phones |
| Computers | Electronics | 3 | Laptops |
| Kitchen | Home | 4 | Cookware |
| Furniture | Home | 5 | Chairs |
| Men | Clothing | 6 | Shirts |
| Women | Clothing | 7 | Dresses |
Table click_log
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| product_id | INT | NOT NULL |
| event_time | TIMESTAMP | NOT NULL |
Sample data:
| event_time | product_id | user_id |
|---|---|---|
| 2025-05-05 10:00:00 | 1 | 101 |
| 2025-05-06 11:00:00 | 3 | 101 |
| 2025-05-07 09:30:00 | 2 | 102 |
| 2025-05-08 14:15:00 | 4 | 102 |
| 2025-05-09 16:45:00 | 4 | 103 |
| 2025-05-10 12:00:00 | 5 | 103 |
| 2025-05-11 13:20:00 | 1 | 103 |
| 2025-05-12 08:05:00 | 6 | 104 |
| 2025-05-13 18:30:00 | 7 | 104 |
| 2025-06-02 10:00:00 | 6 | 105 |
| 2025-04-30 09:00:00 | 2 | 106 |
| 2025-05-15 17:10:00 | 4 | 101 |
| 2025-05-16 11:25:00 | 5 | 107 |
Expected output
One row per department, showing the department name and the count of distinct users who clicked any product in that department between 2025-05-01 and 2025-05-31 inclusive. Users who clicked multiple products in the same department are counted once for that department.
| department | distinct_user_count |
|---|---|
| Clothing | 1 |
| Electronics | 3 |
| Home | 4 |
Hints
- Join click_log to products using product_id to know which department each click belongs to.
- Use COUNT(DISTINCT user_id) grouped by department, with a WHERE clause limiting event_time to the given date range.