Calculate retention rate per user
Last updated: September 17, 2025
Quick Overview
Write a query to compute retention rate grouped by date, handling edge cases like nulls and duplicates.
Goldman Sachs
September 17, 2025136
6
4,234 solved
Write a query to compute retention rate grouped by date, handling edge cases like nulls and duplicates.
Data manipulation questions at Goldman Sachs test your ability to work with real-world datasets. This Take-home Project question evaluates your SQL proficiency, understanding of data modeling, and ability to derive insights from raw data.
What the Interviewer Expects
- Use advanced SQL features: window functions, CTEs, subqueries
- Write efficient queries that avoid common performance pitfalls
- Handle complex data transformations with multiple joins and aggregations
- Discuss indexing strategy and query optimization
- Address data quality issues: duplicates, missing values, outliers
Key Topics to Cover
How to Approach This
- Clarify the schema and expected output format before writing queries.
- Use CTEs (WITH clauses) to break complex queries into readable steps.
- Consider window functions (ROW_NUMBER, RANK, LAG, LEAD) for ranking and sequential analysis.
- Watch for NULLs, duplicates, and edge cases in JOINs and GROUP BY.
- For pandas, prefer vectorized operations over row-by-row iteration.
Possible Follow-up Questions
- How would you handle slowly changing dimensions in this scenario?
- How would you validate the correctness of your query results?
- What would you do if this query needs to run every 5 minutes?
Sharpen Your Skills on Codemia
Practice similar problems with our interactive workspace, get AI feedback, and track your progress.
Practice SQL ProblemsSample Answer
Problem Understanding
## Data Model & Requirements Assuming a typical events table structure: - `events(user_id, event_date, event_type)` - user activity records - OR a si...
Approach: Step-by-Step Strategy
## Strategy for Retention Calculation **Step 1: Clean & Deduplicate** - Remove rows with NULL `user_id` or invalid `event_date` - Deduplicate: Per us...
Submit Your Answer
