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.

EasyTechnical

54 practiced

Using the `orders(order_id, user_id, amount)` table, write a SQL query (Postgres) that ranks users by total spend and returns the top 10 using a window function. Explain difference between RANK(), DENSE_RANK() and ROW_NUMBER() and which you chose.

EasyTechnical

55 practiced

Table `events(user_id, event_ts TIMESTAMP WITH TIME ZONE)`. Write a Postgres SQL query that buckets events into calendar weeks (using ISO weeks) and returns counts per week. Explain how you handle timezones and daylight savings issues so analytics align with a business timezone.

MediumTechnical

72 practiced

Given `orders(order_id, user_id, amount, created_at)`, write a SQL query (Postgres) that returns each user's first order and its amount. Implement it once with a correlated subquery and once using window functions (ROW_NUMBER). Discuss performance trade-offs for large tables.

MediumTechnical

61 practiced

Write SQL (Postgres) to sessionize web events into sessions per user using a 30-minute inactivity gap. Input: `events(user_id, event_ts)`. Output: session_id, user_id, session_start, session_end, event_count. Use window functions and explain how you generate session identifiers.

HardSystem Design

75 practiced

Design materialized view refresh strategies for a near-real-time dashboard (<2 minutes latency) on 10TB of event data. Compare full refresh, incremental refresh, and streaming update to materialized views. Show SQL patterns (MERGE into mv) for incremental refresh and discuss consistency at scale.

Unlock Full Question Bank

Get access to hundreds of SQL for Data Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.