← Discord Interview Insights

Discord·Software Engineer·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

Discord data engineer interview, got a SQL question that looked simple on the surface but had a specific aggregation requirement that tripped me up a bit.

Questions Asked (1)

Q1

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

Product Analytics & MetricsData Modeling
Author's notes

The two-step aggregation part is where I fumbled initially.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, filter the table to the year 2020 and extract the ISO week using a function like EXTRACT(WEEK FROM timestamp). Then, group by that week and count the views (or sum if there's a view count column) to get weekly totals, and finally compute the average of those totals using a subquery or CTE.

Pro tip: Clarify whether 'views' means counting rows or summing a separate views column, and mention that ISO weeks can span year boundaries, so filtering by year might need adjustment for weeks that start in 2019 or end in 2021.

1. Clarify the definition of a view

Ask whether each row represents a single view or if there is a separate views count column. This determines whether to use COUNT(*) or SUM(views).

2. Filter for the year 2020

Use a WHERE clause to restrict the data to timestamps within 2020. Be mindful of time zones and whether to use calendar year or ISO year.

3. Extract ISO week and aggregate

Use EXTRACT(WEEK FROM timestamp) or equivalent to get the ISO week number, then GROUP BY that week and compute the total views per week.

4. Average the weekly totals

Wrap the weekly aggregation in a subquery or CTE and compute the average of the weekly sums using AVG().

5. Consider edge cases and validate

Check for weeks with zero views (if they should be included) and ensure the query handles ISO weeks that cross year boundaries correctly.

Key Points to Mention

  • Use of EXTRACT(WEEK FROM timestamp) or DATE_PART('week', timestamp) for ISO week extraction.
  • Difference between COUNT(*) and SUM(views) based on table schema.
  • Filtering by year: using EXTRACT(YEAR FROM timestamp) = 2020 vs. ISO year considerations.
  • Grouping by week and then averaging the weekly totals using a subquery or CTE.
  • Handling of weeks that span year boundaries (e.g., ISO week 53).
  • Potential need to include weeks with zero views if the business requires it.

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