# The Weekly Pulse

> Notifications by platform and day. When does the audience actually show up?

Canonical URL: <https://datadriven.io/problems/the_weekly_pulse>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Build a notification volume breakdown pivoted by platform and day of week, scoped to users enrolled in at least one experiment. Rows should be days of the week (0=Sunday through 6=Saturday), with separate count columns for ios, android, and web. Return the day of week and each platform's count.

## Worked solution and explanation

### Why this problem exists in real interviews

The core test is combining rows from push_notifs and experiments using pivot and conditional aggregation, where a fan-out on the join key will silently corrupt aggregates. This appears in mid-level screens to separate careful thinkers from syntax-first candidates.

---

### Break down the requirements

#### Step 1: Identify experiment users

Scope to users enrolled in at least one experiment with a semi-join: WHERE pn.user_id IN (SELECT DISTINCT user_id FROM experiments). A semi-join avoids the row-fan-out that a JOIN on experiments would cause when a user has multiple experiment enrollments (which would over-count notifications).

#### Step 2: Filter notifications to experiment users

Derive the day of week with strftime('%w', sent_at), which yields 0=Sunday through 6=Saturday per the prose, cast to INTEGER for a clean numeric column and grouping key.

#### Step 3: Extract day of week

Pivot platform into three conditional aggregates. Normalize with LOWER(pn.platform) because the seed stores mixed-case dirty values (iOS/ios, Android/android, WEB/web); case-sensitive equality silently drops the capitalized variants. Non-platform noise (basic, premium) is naturally excluded.

#### Step 4: Pivot by platform

Use SUM(CASE WHEN ... THEN 1 ELSE 0 END) so each bucket is an unambiguous count; GROUP BY the day_of_week expression and ORDER BY day_of_week for deterministic ascending output.

---

### The solution

**Semi-join to experiments, pivot by platform**

```sql
SELECT
    CAST(strftime('%w', pn.sent_at) AS INTEGER) AS day_of_week,
    SUM(CASE WHEN LOWER(pn.platform) = 'ios'     THEN 1 ELSE 0 END) AS ios_count,
    SUM(CASE WHEN LOWER(pn.platform) = 'android' THEN 1 ELSE 0 END) AS android_count,
    SUM(CASE WHEN LOWER(pn.platform) = 'web'     THEN 1 ELSE 0 END) AS web_count
FROM push_notifs pn
WHERE pn.user_id IN (SELECT DISTINCT user_id FROM experiments)
GROUP BY CAST(strftime('%w', pn.sent_at) AS INTEGER)
ORDER BY day_of_week
```

> **Cost Analysis**
>
> At `push_notifs` (100,000,000 rows), `experiments` (2,000,000 rows), a full table scan is expensive. Partition pruning (if the table is partitioned on the filter column) is the first optimization. A covering index on the `GROUP BY` + filter columns eliminates random I/O. Consider a materialized view for repeated dashboard queries.

> **Interviewers Watch For**
>
> Interviewers evaluate whether you translate the English requirements into the correct SQL clauses on the first attempt. They watch for clean syntax, correct column references, and whether you verify edge cases before declaring the query complete.

> **Common Pitfall**
>
> The most common mistake is misreading the prompt's filtering or grouping requirements. Double-check which columns to group by, which to aggregate, and whether the output should be filtered with `WHERE` (before grouping) or `HAVING` (after grouping).

---

## Common follow-up questions

- What happens to your result if push_notifs.campaign contains NULLs for some rows? _(Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on campaign.)_
- What happens to your CASE expressions if a new category value appears in title, platform? _(Tests whether the candidate recognizes that hard-coded CASE values miss future categories.)_
- With millions of distinct values in push_notifs.notif_id, what index strategy would you use to keep this query performant? _(Tests indexing knowledge specific to high-cardinality columns like notif_id.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_weekly_pulse)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.