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

Explain a case where a CTE being materialized causes a performance regression. Provide the original CTE-based SQL and a rewrite (inline subquery or temp table) that performs better in engines where CTEs are materialized (e.g., older Postgres versions).

EasyTechnical

0 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

0 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

0 practiced

Table `events(event_id, user_id, payload JSONB)` where payload has an array of purchased product IDs under key 'items'. Write Postgres SQL to explode the items array and count purchases per product_id. Include use of LATERAL and show how to index JSONB for better performance.

EasyTechnical

0 practiced

List and explain best practices for writing reproducible analytical SQL queries for dashboards (naming, parameterization, tests, idempotency). Provide an example showing a parameterized date range using a single CTE as a source-of-truth for a dashboard query.

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.