Data Analyst Interview Questions & Model Answers

To calculate 30-day retention, I first create a Common Table Expression (CTE) defining each user’s initial signup date using `MIN(activity_date) OVER(PARTITION BY user_id)`. Then, I compute the date difference (`DATEDIFF`) between the subsequent activity dates and that cohort signup date. Finally, I group by signup cohort week/month and calculate the count of distinct active users at Day 30 divided by the initial cohort size.

"I explain it using the classic summer analogy: ice cream sales and shark attacks both spike during July and August. They are strongly correlated, but eating ice cream doesn’t cause shark attacks—the hidden underlying variable is warm weather driving more people to the beach. In business, if marketing email opens correlate with high purchases, we must run a controlled A/B holdout test to prove whether the emails drove the purchase or if active loyal buyers were simply more likely to open our emails anyway."