## Problem You have a `purchases` table. Find all users who made at least one purchase in every calendar month of the year 2023. ```sql -- Table: purchases -- user_id INT -- purchase_date DATE -- amount DECIMAL SELECT user_id FROM purchases WHERE YEAR(purchase_date) = 2023 GROUP BY user_id HAVING COUNT(DISTINCT MONTH(purchase_date)) = 12 ORDER BY user_id; ``` **Example result:** ``` user_id ------- 101 334 ``` ## Follow-ups 1. Rewrite the query to work for any year passed as a parameter, not just 2023. 2. The business defines "loyal" as purchasing in 10 or more of 12 months. Adjust the query. 3. Some users have duplicate purchases on the same day. Does your query still return the correct result? Why? 4. Write a Python equivalent using `pandas` groupby that produces the same output from a DataFrame.

InterviewDB Question

Loyal Members: Identify Users Who Made Purchases in Every Month of a Given Year

Onsite

Question Details

Problem You have a purchases table. Find all users who made at least one purchase in every calendar month of the year 2023. Example result: Follow-ups Rewrite the query to work for any year passed as a parameter, not just 2023. The business defines "…

Full Details

🔒

Unlock full leaked content

Full insider details, leaked discussions, and candidate experiences.

Get Premium →

Topics

Coding Onsite

← All Sofi questions Sofi Software Engineer Questions → All Hash Table Questions →

Loyal Members: Identify Users Who Made Purchases in Every Month of a Given Year

Question Details

Full Details

Topics

More from Sofi