SQL for Data Analysis Questions
Using SQL as a tool for data analysis and reporting. Focuses on writing queries to extract metrics, perform aggregations, join disparate data sources, use subqueries and window functions for trends and rankings, and prepare data for dashboards and reports. Includes best practices for reproducible analytical queries, handling time series and date arithmetic, basic query optimization considerations for analytic workloads, and when to use SQL versus built in reporting tools in analytics platforms.
MediumTechnical
0 practiced
Write SQL to produce a cohort heatmap in long format: columns (cohort_week, week_offset, pct_active) for weeks 0..12. Input: users(user_id, signup_date) and events(user_id, occurred_at). Use CTEs and explain how you ensure small cohorts don't mislead percentages.
MediumTechnical
0 practiced
Write SQL that pivots event counts for a single event_name (e.g., 'login') across the last 7 days into columns day_0..day_6 for dashboard consumption. Use conditional aggregation or a pivot operator and ensure days with zero counts still appear.
HardTechnical
0 practiced
Your distinct-user query: SELECT country, COUNT(DISTINCT user_id) FROM events WHERE occurred_at >= current_date - interval '30 days' GROUP BY country is slow on a 1B-row table. Propose SQL and architectural optimizations, including examples using approximate_count_distinct or pre-aggregations. Explain trade-offs.
EasyTechnical
0 practiced
Write a SQL query that returns daily active users (DAU) for each of the past 14 calendar days (columns: date, dau) from the events table below. Include days with zero activity in the result and explain which technique you used to guarantee missing-days are present.
sql
events(event_id, user_id, event_name, occurred_at timestamp)HardTechnical
0 practiced
Explain how approximate distinct algorithms like HyperLogLog (HLL) work at a high level, and write SQL showing how you'd use a built-in approx_count_distinct (or equivalent) to compute daily unique users on a huge events table. Discuss trade-offs and validation approaches.
Unlock Full Question Bank
Get access to hundreds of SQL for Data Analysis interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.