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
You have web_events(user_id_web, device_id, occurred_at) and mobile_events(user_id_mobile, device_id, occurred_at) plus mapping device_user_map(device_id, user_id). Write SQL that unifies activity to canonical user_id and computes unified DAU over the last 30 days. Explain assumptions about device_id reuse and anonymous users.
MediumTechnical
0 practiced
The events table contains duplicated rows due to client retries. Write SQL to deduplicate events, keeping the latest record per logical_event_id (assume a column logical_event_id exists) and produce a deduped_events CTE. Also produce a small summary of how many duplicates were removed per day.
MediumTechnical
0 practiced
Your dashboard query that joins events to users and filters a date range is slow on a 200M-row events table. As a PM working with an analyst, outline a step-by-step approach to diagnose and optimize the query (SQL-level and infra-level). Mention tools/queries you'd run to identify bottlenecks.
HardTechnical
0 practiced
Given an experiment assignment table assignments(user_id, experiment_id, variant, assigned_at) and events(user_id, occurred_at, event_name), write SQL to compute 7-day retention for treatment vs control. Describe how you'll handle users who cross over variants or are reassigned after events.
HardTechnical
0 practiced
Design an idempotent incremental aggregation SQL job to update a daily summary table with DAU and new_user_count. Provide pseudocode using MERGE or atomic upserts, describe watermarking for late-arriving events, and discuss how to handle backfills.
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.