Compute unique visitors by department
Company: Amazon
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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:
1. products(product_id, department, category, subcategory) where department > category > subcategory form a parent–child hierarchy.
2. 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.)
Tables
products(product_id INT, department VARCHAR(50), category VARCHAR(50), subcategory VARCHAR(50))
click_log(user_id INT, product_id INT, event_time TIMESTAMP)
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.