Day 7: Review – Week 1

Day 7 — Review – Week 1

Coding problem

Solution outline (coding)

• List Days 1–6 problems; mark which felt slow, buggy, or pattern-unclear.
• Re-solve 2–3 of those timed (no peeking), then compare to the canonical approach in notes.
• Write one line per problem: pattern name, time/space you stated, and one edge case to remember.

Time complexity: Varies — goal is fluency, not a single Big-O.

Space complexity: Varies — depends on which problems you repeat.

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

  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.
user_events(user_id, ts, event_type). BigQuery: (1) data-quality query to flag null user_id, null ts, future ts, or ts < previous ts per user; (2) daily funnel (view → add_to_cart → purchase) with counts and drop-off %.

Tables implied by the prompt:

• user_events(user_id, ts, event_type)
• funnel(view → add_to_cart → purchase)

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.
• Nulls: COALESCE for dimensions; decide how unknowns appear in the result.
• Structure: CTEs (WITH) — one step per CTE; validate on a tiny slice (counts, nulls, duplicates).

-- Run as two saved queries in practice: (A) DQ flags, (B) funnel.
WITH dq AS (
  SELECT
    user_id,
    ts,
    event_type,
    CASE
      WHEN user_id IS NULL THEN 'null_user'
      WHEN ts IS NULL THEN 'null_ts'
      WHEN ts > CURRENT_TIMESTAMP() THEN 'future_ts'
      WHEN ts < LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) THEN 'out_of_order'
      ELSE 'ok'
    END AS dq_flag
  FROM user_events
),
funnel AS (
  SELECT
    DATE(ts) AS d,
    COUNTIF(event_type = 'view') AS views,
    COUNTIF(event_type = 'add_to_cart') AS carts,
    COUNTIF(event_type = 'purchase') AS purchases
  FROM user_events
  GROUP BY d
)
SELECT 'dq' AS q, CAST(NULL AS DATE) AS d, dq_flag AS detail, COUNT(*) AS cnt
FROM dq
WHERE dq_flag != 'ok'
GROUP BY dq_flag
UNION ALL
SELECT 'funnel', d, NULL, views FROM funnel;