Day 14: Review – Week 2

Day 14 — Review – Week 2

Coding problem

Solution outline (coding)

• Review Days 8–12: two pointers, sliding window, and binary search templates.
• Blind-write each template once (boundaries, off-by-one) under time pressure.
• Note one failure mode per pattern (e.g. duplicate triplets, empty window, integer overflow in mid).

Time complexity: Varies.

Space complexity: Varies.

class ReviewDay:
  """Practice / review: Review – Week 2."""

  def practice_plan(self):
    return [
      "Pick 2–3 problems from this phase; re-solve timed without notes.",
      "For each: pattern name, time/space complexity, one alternative approach.",
    ]


# Input:  (your choice of problems from this week or phase)
# Output: a short list of gaps to drill before the next session

SQL interview practice

1. Interview question

Companies / track: Review / mixed (see weekly theme)

This is a review / mixed day. Expect SQL that blends data quality, funnels, and metric definitions—the same mix you see across consumer tech and ads analytics.

What you are asked to write (SQL prompt):

Review / mixed week — use the same tables and deliverables as in a standard onsite SQL round.
pageviews, sessions, orders tables. BigQuery: (1) daily active users by country with 7-day rolling avg; (2) session length buckets; (3) time-to-conversion histogram; focus on window usage and efficient scans.

Tables implied by the prompt:

• Infer schemas from the prompt and state them before coding.

Engine: BigQuery — use its date, array, and approximate functions as documented.

2. Solution outline

• Clarify out loud: result grain (one row per what?), join keys, time zone, and any ORDER BY / LIMIT / tie-breakers.
• Map Review to SQL: say the relational equivalent (e.g. hash map → GROUP BY + key; two pointers → ordered window + filter).
• Filter time first: predicate on DATE(ts) / partition column before heavy joins; state the window in plain English.
• Windows: align PARTITION BY / ORDER BY with the business rule; use QUALIFY in BigQuery when you need top-N per group.
• Sessions / streaks: often LAG/LEAD or gap flags, then aggregate; check boundary dates.
• Structure: CTEs (WITH) — one step per CTE; validate on a tiny slice (counts, nulls, duplicates).

WITH dau AS (
  SELECT DATE(ts) AS d, country, COUNT(DISTINCT user_id) AS dau
  FROM pageviews
  GROUP BY d, country
)
SELECT country, d, dau,
  AVG(dau) OVER (PARTITION BY country ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7d_avg
FROM dau;