CS145: Your Data Systems Journey

Master the core. Pick a technical path. Build your projects.

A left-to-right map: two blue boxes are CS145 content (core curriculum with Project 1, then a Project 2 choice of Science or the Systems path NanoMem), a grey box is what you keep learning beyond the course, a green box is the careers it leads to.

Figure 1. Core database skills build toward a project path, and each path maps to the industry roles that use it.

After CS145: Interview Questions You Can Answer

These representative interview questions use familiar company contexts. By the end of CS145, you can solve each one and handle the scale follow-up.

SQL & Analytics

Amazon · Top products query

Products with over $1M revenue in Q4 but under $100K in Q1.

Follow-up: optimize for a 10 TB orders table; which indexes?

Hints: orders(order_id, product_id, amount, date), products(product_id, name) · EXTRACT/QUARTER · GROUP BY with HAVING

Meta · Friend recommendations

Friends-of-friends but not direct friends, ranked by mutual friend count.

Follow-up: 30 s on 1M users; optimize for 500M.

Hints: friendships(user1_id, user2_id) bidirectional · self-joins · COUNT DISTINCT · LEFT JOIN with NULL check

Netflix · Binge-watching detection

Users who watched over 3 episodes of one show in any 24-hour window.

Follow-up: episodes across midnight, or across time zones?

Hints: views(user_id, show_id, episode_id, timestamp) · window functions (LAG / LEAD) · timestamp diffs

Systems Design

Google · URL deduplication

Check if a URL was crawled before. Handle 100B URLs.

Follow-up: normalization: www vs non-www, http vs https, trailing slash.

Hints: 100B × 100 bytes = 10 TB (no fit in memory) · Bloom filters (1% false positive ok) · distributed hash tables

Uber · Distributed event counter

Real-time ride counter across 5 data centers. Under 1 s lag, 99.99% uptime.

Follow-up: accuracy or availability during a network partition? Why?

Hints: partitions between DCs · eventually consistent counters · CRDTs · write-through cache

You'll read, debug, and write queries like this

Example: Finding Power Users

The power-users query in the three parts it is built from, each feeding the next: user_stats rolls every listen up per user; power_users keeps users spanning at least three genres and ranks them into deciles by minutes; the final SELECT joins back to names and returns the top decile.

Figure 2. A production SQL pattern: two CTEs compute per-user aggregates and a decile rank, then an outer SELECT joins names and filters to the top decile.

WITH user_stats AS (
  SELECT
    l.user_id,
    COUNT(DISTINCT l.song_id) AS unique_songs,
    COUNT(DISTINCT s.genre) AS unique_genres,
    COUNT(*) AS total_listens
  FROM Listens AS l
  JOIN Songs AS s ON l.song_id = s.song_id
  GROUP BY l.user_id
),
power_users AS (
  SELECT
    user_id,
    unique_songs,
    unique_genres,
    total_listens,
    NTILE(10) OVER (ORDER BY total_listens DESC) AS decile
  FROM user_stats
  WHERE unique_genres >= 3
)
SELECT
  u.name,
  p.unique_songs,
  p.unique_genres,
  p.total_listens
FROM power_users AS p
JOIN Users AS u ON p.user_id = u.user_id
WHERE p.decile = 1
ORDER BY p.total_listens DESC;

From an event to an analytical result

Spotify's published data platform

Spotify's clients publish typed events into an event-delivery system. Its data platform provisions queues, anonymization pipelines, and streaming jobs. Scheduled workflows run in BigQuery, Flink, or Dataflow.

A Spotify event moves from a client into event delivery, through anonymization and stream processing, and then into systems such as BigQuery, Flink, or Dataflow for analysis.

Figure 3. Spotify's published data platform collects typed client events, moves them through managed queues and processing jobs, and makes the resulting data available for analysis.