Coding questions · CVS Health
Write SQL for dedup and purchase shares
This question evaluates proficiency in SQL data manipulation skills including entity deduplication, canonicalization rules, window functions, common table expressions, joins, and aggregation to derive per-user purchase counts and distributions.
You are given two tables with intentional duplicates. Write SQL to: (a) identify duplicate user_ids and produce a canonical, deduplicated users set; (b) list users with purchase_count >= 2 using a deduplicated view of purchases; (c) produce a distribution table of purchase_count per user (including users with 0 purchases) and the percentage each bucket represents out of all unique users.
Schema
- users(user_id INT, name VARCHAR, signup_date DATE)
- purchases(order_id INT, user_id INT, purchased_at DATE, amount_cents INT)
Sample data (small, but representative)
users
user_id | name | signup_date
101 | Alice | 2025-01-01
101 | Alice M | 2025-01-01
102 | Bob | 2025-01-03
103 | Cathy | 2025-01-04
103 | C. Li | 2025-01-04
104 | Dan | 2025-01-05
purchases
order_id | user_id | purchased_at | amount_cents
7001 | 101 | 2025-01-10 | 1200
7001 | 101 | 2025-01-10 | 1200 -- duplicate row
7002 | 102 | 2025-01-11 | 500
7003 | 103 | 2025-01-11 | 700
7003 | 103 | 2025-01-11 | 700 -- duplicate row
7004 | 103 | 2025-01-12 | 400
7005 | 101 | 2025-01-13 | 350
Requirements and hints
- (a) Output two result sets: one listing duplicate user_id rows (i.e., all users where count(*) over user_id > 1) and one CTE/view deduped_users with exactly one row per user_id. Choose the canonical row as the earliest signup_date; if tied, pick lexicographically smallest name. Show your window-function logic explicitly.
- (b) Before counting purchases, deduplicate purchases so each order_id contributes at most once (keep the earliest purchased_at per order_id). Use this deduped_orders CTE to compute purchase_count per user and then return all users with purchase_count >= 2.
- (c) Using deduped_users LEFT JOIN deduped_orders, compute for each exact purchase_count (0,1,2,...) the number of users and percent_of_users = number_in_bucket / total_unique_users, rounded to 2 decimals. Return rows sorted by purchase_count ascending. Ensure users with 0 purchases appear.
Deduplicate users and identify duplicate rows
You are given a users table that intentionally contains duplicate rows (the same user_id may appear more than once, for example because the same person was recorded with slightly different names). A purchases table is also available but is not needed for this sub-question.
Write a single PostgreSQL query, using window functions, that returns a combined result set with two kinds of rows:
- Duplicate rows — every row belonging to a
user_idthat appears more than once inusers. Tag each of these withrecord_type = 'duplicate'. - Deduplicated (canonical) rows — exactly one row per
user_id(build this as a CTE nameddeduped_users). The canonical row for auser_idis the one with the earliestsignup_date; if two rows tie onsignup_date, pick the row whosenamesorts first under the database's default collation (ORDER BY signup_date, name). Tag each of these withrecord_type = 'deduped'.
UNION the two sets together.
Output columns (in this order): record_type, user_id, name, signup_date.
Required sort order: all 'duplicate' rows first, then all 'deduped' rows; within each group order by user_id ascending, then name ascending.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| name | VARCHAR(50) | NOT NULL |
| signup_date | DATE | NOT NULL |
Sample data:
| name | signup_date | user_id |
|---|---|---|
| Alice | 2025-01-01 | 101 |
| Alice M | 2025-01-01 | 101 |
| Bob | 2025-01-03 | 102 |
| Cathy | 2025-01-04 | 103 |
| C. Li | 2025-01-04 | 103 |
| Dan | 2025-01-05 | 104 |
Table purchases
| column | type | constraints |
|---|---|---|
| order_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| purchased_at | DATE | NOT NULL |
| amount_cents | INT | NOT NULL |
Sample data:
| amount_cents | order_id | purchased_at | user_id |
|---|---|---|---|
| 1200 | 7001 | 2025-01-10 | 101 |
| 1200 | 7001 | 2025-01-10 | 101 |
| 500 | 7002 | 2025-01-11 | 102 |
| 700 | 7003 | 2025-01-11 | 103 |
| 700 | 7003 | 2025-01-11 | 103 |
| 400 | 7004 | 2025-01-12 | 103 |
| 350 | 7005 | 2025-01-13 | 101 |
Expected output
All duplicate user rows (record_type='duplicate') followed by the canonical one-row-per-user deduplicated set (record_type='deduped'), each group sorted by user_id then name.
| name | record_type | signup_date | user_id |
|---|---|---|---|
| Alice | duplicate | 2025-01-01 | 101 |
| Alice M | duplicate | 2025-01-01 | 101 |
| Cathy | duplicate | 2025-01-04 | 103 |
| C. Li | duplicate | 2025-01-04 | 103 |
| Alice | deduped | 2025-01-01 | 101 |
| Bob | deduped | 2025-01-03 | 102 |
| Cathy | deduped | 2025-01-04 | 103 |
| Dan | deduped | 2025-01-05 | 104 |
Hints
- Use COUNT(*) OVER (PARTITION BY user_id) to flag any user_id that appears more than once, and ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY signup_date, name) to pick the one canonical row (rn = 1).
- PostgreSQL won't accept a CASE expression in an ORDER BY that sits directly after UNION — wrap the UNION in a CTE/subquery and order in the outer query.
- Build the two row sets separately, then UNION ALL them, tagging each with the right record_type literal.
Users with at least two purchases using deduplicated orders
Using the same users and purchases tables (which contain intentional duplicates), write a SQL query that:
- Builds a CTE deduped_users with exactly one canonical row per user_id (as in the previous question: earliest signup_date, then lexicographically smallest name).
- Builds a CTE deduped_orders that deduplicates purchases so that each order_id contributes at most once, keeping the earliest purchased_at per order_id.
- Counts the number of deduplicated orders per user (purchase_count) and returns all users with purchase_count >= 2, including their user_id, name, and purchase_count. Order the final result by user_id ascending.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| name | VARCHAR(50) | NOT NULL |
| signup_date | DATE | NOT NULL |
Sample data:
| name | signup_date | user_id |
|---|---|---|
| Alice | 2025-01-01 | 101 |
| Alice M | 2025-01-01 | 101 |
| Bob | 2025-01-03 | 102 |
| Cathy | 2025-01-04 | 103 |
| C. Li | 2025-01-04 | 103 |
| Dan | 2025-01-05 | 104 |
Table purchases
| column | type | constraints |
|---|---|---|
| order_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| purchased_at | DATE | NOT NULL |
| amount_cents | INT | NOT NULL |
Sample data:
| amount_cents | order_id | purchased_at | user_id |
|---|---|---|---|
| 1200 | 7001 | 2025-01-10 | 101 |
| 1200 | 7001 | 2025-01-10 | 101 |
| 500 | 7002 | 2025-01-11 | 102 |
| 700 | 7003 | 2025-01-11 | 103 |
| 700 | 7003 | 2025-01-11 | 103 |
| 400 | 7004 | 2025-01-12 | 103 |
| 350 | 7005 | 2025-01-13 | 101 |
Expected output
All canonical users who have at least two deduplicated orders, using deduped_users and deduped_orders CTEs.
| name | purchase_count | user_id |
|---|---|---|
| Alice | 2 | 101 |
| C. Li | 2 | 103 |
Hints
- First deduplicate users with ROW_NUMBER() over user_id and keep the first row per user_id.
- Use ROW_NUMBER() over order_id to deduplicate purchases, then COUNT(*) per user_id and filter purchase_count >= 2.
Purchase count distribution per user using deduplicated data
Using the same users and purchases tables, build on the deduplication logic to compute a distribution of purchase counts per user. Specifically:
- Create deduped_users as a canonical set of users with exactly one row per user_id (earliest signup_date, then lexicographically smallest name).
- Create deduped_orders where each order_id appears at most once, keeping the earliest purchased_at per order_id.
- Using deduped_users LEFT JOIN deduped_orders, compute for each user their purchase_count (number of deduplicated orders). Users with no purchases should have purchase_count = 0.
- Aggregate into a distribution table with one row per exact purchase_count (0, 1, 2, ...), including:
- purchase_count
- users_in_bucket = number of users with that purchase_count
- percent_of_users = users_in_bucket / total_unique_users, rounded to 2 decimal places. Return the rows sorted by purchase_count ascending.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| name | VARCHAR(50) | NOT NULL |
| signup_date | DATE | NOT NULL |
Sample data:
| name | signup_date | user_id |
|---|---|---|
| Alice | 2025-01-01 | 101 |
| Alice M | 2025-01-01 | 101 |
| Bob | 2025-01-03 | 102 |
| Cathy | 2025-01-04 | 103 |
| C. Li | 2025-01-04 | 103 |
| Dan | 2025-01-05 | 104 |
Table purchases
| column | type | constraints |
|---|---|---|
| order_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| purchased_at | DATE | NOT NULL |
| amount_cents | INT | NOT NULL |
Sample data:
| amount_cents | order_id | purchased_at | user_id |
|---|---|---|---|
| 1200 | 7001 | 2025-01-10 | 101 |
| 1200 | 7001 | 2025-01-10 | 101 |
| 500 | 7002 | 2025-01-11 | 102 |
| 700 | 7003 | 2025-01-11 | 103 |
| 700 | 7003 | 2025-01-11 | 103 |
| 400 | 7004 | 2025-01-12 | 103 |
| 350 | 7005 | 2025-01-13 | 101 |
Expected output
Distribution of deduplicated purchase counts per canonical user, including users with zero purchases, and the percentage of total users in each bucket.
| percent_of_users | purchase_count | users_in_bucket |
|---|---|---|
| 0.25 | 0 | 1 |
| 0.25 | 1 | 1 |
| 0.5 | 2 | 2 |
Hints
- First compute per-user purchase_count by LEFT JOINing deduped_users to deduped_orders and grouping by user_id.
- Then aggregate those counts into a distribution, compute the total number of users, and derive the percentage via a CROSS JOIN to the total.