Glean·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

SQL-heavy technical screen for a DS role at Glean. Two questions, both around user activity metrics, and the follow-ups pushed into territory I hadn't fully prepped for.

Questions Asked (2)

Write a SQL query to compute Daily Active Users (distinct logins per calendar day) over a date range, making sure every date in the range appears in the output even if there were zero logins that day. How would you generate a date spine in PostgreSQL if you don't have a calendar table?

Product Analytics & MetricsData Modeling

Author's notes

The zero-login days requirement is what trips people up here and I almost missed it.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and the definition of a daily active user, then outline a two-part solution: generate a complete date spine for the range and left join aggregated distinct user counts to it. For PostgreSQL, show how to use generate_series to create the date spine without a calendar table, and emphasize that the left join ensures zero-login days appear with a count of 0.

Pro tip: Mention that you'd validate the date spine covers the exact range (inclusive) and consider timezone handling for 'calendar day'—this shows you think about edge cases that often break DAU metrics in production.

1. Clarify requirements and schema

Confirm the login table structure, the definition of 'active' (distinct user logins), and the date range boundaries (inclusive/exclusive). Ask about timezone if not specified.

2. Generate a date spine

In PostgreSQL, use generate_series(start_date, end_date, interval '1 day') to produce every date in the range. Cast to date to ensure calendar-day granularity.

3. Aggregate daily active users

Write a subquery that groups logins by date and counts distinct user IDs. This yields actual DAU per day where logins exist.

4. Left join and fill zeros

Left join the date spine to the aggregated DAU subquery on date, and use COALESCE to replace NULL counts with 0 for days with no logins.

5. Final query and validation

Assemble the full query, ensuring the date spine is the driving table. Optionally, mention indexing the login date column for performance and validating the output covers all dates.

Key Points to Mention

Use generate_series for date spine in PostgreSQL without a calendar table.
Count distinct user IDs per day to compute DAU.
Left join from date spine to aggregated logins to retain all dates.
Use COALESCE or IFNULL to replace NULL with 0 for zero-login days.
Consider timezone conversion for 'calendar day' if timestamps are in UTC.
Ensure the date range is inclusive of both start and end dates.

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

Extend the DAU query to compute a rolling 30-day MAU for each calendar date, where MAU is defined as distinct users who logged in within the 30 days ending on and including that date. Then discuss: if the product sees little to no weekend usage, what problems does this L30D window create and what alternative definitions might work better?

Product Analytics & MetricsA/B Testing & ExperimentationTechnical Trade-offs

Author's notes

The rolling window part I handled okay, window function with a range between interval.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by writing a clear SQL query that computes the rolling 30-day distinct user count for each date, using a self-join or window function with a date range. Then, discuss the implications of low weekend usage on the L30D metric, highlighting issues like artificial drops and misaligned business cycles, and propose alternative definitions such as 30-day active days or 22 business days.

Pro tip: When discussing alternatives, tie them back to business impact—e.g., how a business-day window better reflects true engagement for a B2B product like Glean, where weekend usage is minimal.

1. Clarify the metric and data

Confirm the definition of MAU (distinct users logging in within 30 days) and the available data (login events with user_id and date).

2. Write the SQL query

Use a self-join or window function to count distinct users for each date over the preceding 30 days, ensuring the window includes the current date.

3. Analyze weekend usage impact

Explain how low weekend usage causes the L30D window to include irrelevant days, leading to inflated or deflated MAU and misleading trends.

4. Propose alternative definitions

Suggest alternatives like rolling 30 business days, 30 active days (excluding zero-usage days), or a 4-week window aligned with business cycles.

5. Discuss trade-offs and recommendations

Compare alternatives in terms of accuracy, interpretability, and alignment with business goals, and recommend the best fit for Glean.

Key Points to Mention

Use of DISTINCT COUNT in SQL with a date range condition (e.g., BETWEEN date - INTERVAL '29 days' AND date).
The L30D window includes weekends, which may have near-zero logins, diluting the metric and causing artificial fluctuations.
Alternative: rolling 30 business days (excluding weekends) to better reflect active usage periods.
Alternative: rolling 30 active days (days with any usage) to avoid dilution from zero-usage days.
Consideration of product context: Glean is a B2B product, so weekend usage is likely low, making business-day windows more meaningful.
Trade-offs: business-day windows may be harder to compute and less standard, but more aligned with user behavior.

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