Discord·Software Engineer·Technical Phone Screen·Intermediate

Intermediate

Jun 2026

Summary

Discord data engineer interview with a SQL-heavy technical screen. Just the one question from what I remember, but it was more involved than it looked on the surface.

Questions Asked (1)

You have a server_view table with columns server_id, user_id, and timestamp. Write a SQL query to find the average number of weekly server views for the year 2020. You need to aggregate total views per ISO week first, then average those weekly totals.

Data ModelingProduct Analytics & Metrics

Author's notes

Took me a beat to realize they wanted two layers of aggregation, not just a single GROUP BY.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, filter the server_view table to only include rows from the year 2020. Then, group by the ISO week (using a function like EXTRACT(WEEK FROM timestamp) or DATE_TRUNC('week', timestamp)) and count the total views per week. Finally, compute the average of those weekly counts using a subquery or CTE.

Pro tip: Clarify whether 'views' means distinct users or total view events; if the table has one row per view, COUNT(*) is appropriate. Also, mention that ISO weeks can span year boundaries, so filtering by year on the timestamp might exclude some weeks that partially fall in 2020—consider using ISO year instead.

1. Filter for the year 2020

Use a WHERE clause to restrict the data to timestamps within 2020. Be mindful of time zone if the timestamp is stored in UTC.

2. Group by ISO week

Extract the ISO week number from the timestamp (e.g., EXTRACT(WEEK FROM timestamp)) and group by that week. Alternatively, use DATE_TRUNC('week', timestamp) to get the start of each week.

3. Count views per week

For each week, count the number of views. If each row represents a view, use COUNT(*). If you need distinct users, use COUNT(DISTINCT user_id).

4. Average the weekly counts

Wrap the grouped query in a subquery or CTE and compute the average of the weekly view counts using AVG().

Key Points to Mention

Use of ISO week functions (EXTRACT(WEEK) or DATE_TRUNC('week')) to aggregate by week.
Filtering by year 2020, and potential edge cases with ISO weeks spanning year boundaries.
Distinguishing between total views (COUNT(*)) and distinct users (COUNT(DISTINCT user_id)).
Using a subquery or CTE to first aggregate per week, then average the weekly totals.
Handling NULLs or missing weeks (e.g., weeks with zero views) if the business logic requires including them.
Performance considerations: indexing on timestamp for efficient filtering.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.