Quick Overview

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.

Write SQL for dedup and purchase shares

Company: CVS Health

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

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.

Overview: 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.

Read the full CVS Health Data Scientist interview experience this question came from

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: 1. **Duplicate rows** — every row belonging to a `user_id` that appears more than once in `users`. Tag each of these with `record_type = 'duplicate'`. 2. **Deduplicated (canonical) rows** — exactly **one** row per `user_id` (build this as a CTE named `deduped_users`). The canonical row for a `user_id` is the one with the **earliest `signup_date`**; if two rows tie on `signup_date`, pick the row whose `name` sorts first under the database's default collation (`ORDER BY signup_date, name`). Tag each of these with `record_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.

Tables

users(user_id INT, name VARCHAR(50), signup_date DATE)

purchases(order_id INT, user_id INT, purchased_at DATE, amount_cents INT)

Hints

  1. 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).
  2. 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.

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: 1) 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). 2) 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. 3) 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.

Tables

users(user_id INT, name VARCHAR(50), signup_date DATE)

purchases(order_id INT, user_id INT, purchased_at DATE, amount_cents INT)

Hints

  1. First deduplicate users with ROW_NUMBER() over user_id and keep the first row per user_id.
  2. 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: 1) Create deduped_users as a canonical set of users with exactly one row per user_id (earliest signup_date, then lexicographically smallest name). 2) Create deduped_orders where each order_id appears at most once, keeping the earliest purchased_at per order_id. 3) 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. 4) 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.

Tables

users(user_id INT, name VARCHAR(50), signup_date DATE)

purchases(order_id INT, user_id INT, purchased_at DATE, amount_cents INT)

Hints

  1. First compute per-user purchase_count by LEFT JOINing deduped_users to deduped_orders and grouping by user_id.
  2. Then aggregate those counts into a distribution, compute the total number of users, and derive the percentage via a CROSS JOIN to the total.

Loading coding console...