Structured Query Language Fundamentals and Aggregation Questions

This topic covers core Structured Query Language fundamentals for analytical querying and reporting. Candidates should be able to write correct, readable, and maintainable SELECT queries with filtering using WHERE, sorting with ORDER BY, grouping with GROUP BY, and group filtering with HAVING. They should apply aggregate functions such as COUNT, COUNT DISTINCT, SUM, AVG, MIN, and MAX and understand how NULL values affect results, how empty result sets behave, and when to use different counting approaches. The scope includes date and time filtering, basic cohort segmentation, and common time based comparisons used to compute metrics such as daily active users, average revenue per user, and period over period comparisons. Candidates are expected to use basic joins and join predicates including inner joins and left joins, write simple subqueries and conditional expressions, and perform common data transformation and cleansing patterns to prepare data for analysis. Finally, this topic assesses query readability and maintainability practices such as aliasing and formatting, plus awareness of elementary performance considerations including index usage and avoiding unnecessary full table scans for entry to mid level analytical tasks.

HardTechnical

92 practiced

You need to join a 'merge' table that maps aliases (email, phone, external_id) to canonical user_ids. Design and write SQL to join event data (which may reference alias identifiers) to canonical user_id safely without double-counting or creating duplicates. Explain your chosen dedupe and join strategy and indexing recommendations.

EasyTechnical

51 practiced

Explain what aggregate functions return when applied to an empty result set (e.g., AVG, SUM, COUNT). Then, given a query that returns NULL for total_revenue when no transactions exist, show a SQL pattern that returns 0 instead (use COALESCE). Provide an example.

MediumTechnical

58 practiced

Table: events(event_id, user_id, occurred_at)

Write a SQL query to compute Daily Active Users (DAU): the number of distinct users active each day over a date range. Output columns: day (date), dau. Ensure users with multiple events per day are counted once. Use PostgreSQL-compatible SQL and explain performance considerations for a large events table.

EasyTechnical

44 practiced

Tables:users(user_id PK, created_at TIMESTAMP)orders(order_id, user_id, total_amount, placed_at TIMESTAMP)

Write a SQL query that returns every user_id and their most recent order date (last_order_at). Include users who have never ordered (last_order_at should be NULL). Use an approach that is readable and explain why you chose LEFT JOIN or a subquery.

MediumTechnical

53 practiced

Table: daily_active(day DATE, dau INT)

Given daily DAU counts, write a SQL query to compute week-over-week percentage change in DAU for each week. Use either window functions or self-join. Explain how you handle weeks with zero DAU to avoid division by zero.

Unlock Full Question Bank

Get access to hundreds of Structured Query Language Fundamentals and Aggregation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.