SQL for Data Analysis Questions

Direct answer

Convert each order's UTC instant into the customer's own local wall-clock time using their IANA timezone name (the standard named-timezone database, e.g. America/New_York), then take the date part of that converted timestamp for bucketing. Applying one global UTC offset to every user is wrong for two separate reasons: different users need different offsets, and even a single user's offset changes across a DST (daylight saving time) transition, so any fixed number is only ever correct for part of your users for part of the year.

Structured elaboration

• IANA zone names vs fixed offsets: AT TIME ZONE 'America/New_York' looks up the full DST rule set (the tzdb) for that region and applies whichever offset is correct for that specific timestamp. A fixed offset like -05:00 has no rules, it is just always five hours, which is right for New York in winter (EST) and wrong in summer (EDT).
• Steps: join orders to users on user_id, convert order_date (a UTC instant) into local time with order_date AT TIME ZONE u.timezone, cast the result to DATE, filter and aggregate on that local date.
• Two distinct offset traps, both present in the worked example below:
  1. One offset cannot fit users in different zones at all (Kolkata is UTC+5:30 with no DST; New York alternates between UTC-4 and UTC-5).
  2. Even for a single zone, the correct offset itself changes at the DST boundary, so a number that was right for New York in July is wrong in December.
• Dialect notes: Postgres and DuckDB (with the icu extension loaded) both support timestamp AT TIME ZONE 'IANA/Name' directly, using the underlying tzdata. MySQL needs the timezone tables populated (mysql_tzinfo_to_sql) before CONVERT_TZ() recognizes IANA names; without that load it silently returns NULL instead of erroring. BigQuery uses DATETIME(timestamp_expr, tz_string). Snowflake uses CONVERT_TIMEZONE(tz, timestamp).

Worked example (executed, DuckDB 1.5 with the icu extension loaded, session TimeZone set to UTC)

Seed data: three users in three timezones, and five orders clustered right around the UTC midnight boundary for 2025-11-01 so the correct local date differs from the naive interpretation for most of them.

INSTALL icu; LOAD icu; SET TimeZone='UTC';

CREATE TABLE users (user_id INTEGER, tz_name VARCHAR);
INSERT INTO users VALUES
  (1, 'America/New_York'),
  (2, 'Asia/Kolkata'),
  (3, 'Europe/London');

CREATE TABLE orders (order_id INTEGER, user_id INTEGER, order_date TIMESTAMPTZ, amount_usd DECIMAL(10,2));
INSERT INTO orders VALUES
  (1, 1, TIMESTAMPTZ '2025-11-01 03:30:00 UTC', 100.00),
  (2, 1, TIMESTAMPTZ '2025-11-01 12:00:00 UTC', 50.00),
  (3, 2, TIMESTAMPTZ '2025-10-31 19:00:00 UTC', 75.00),
  (4, 2, TIMESTAMPTZ '2025-11-01 20:00:00 UTC', 200.00),
  (5, 3, TIMESTAMPTZ '2025-11-01 10:00:00 UTC', 30.00);

Correct query:

SELECT o.order_id, o.user_id, o.amount_usd,
       CAST((o.order_date AT TIME ZONE u.tz_name) AS DATE) AS local_date
FROM orders o JOIN users u ON o.user_id = u.user_id
WHERE CAST((o.order_date AT TIME ZONE u.tz_name) AS DATE) = DATE '2025-11-01';

Per-order local conversion (the intermediate step, shown for every order):

Correct result for "orders placed on 2025-11-01" (3 orders, $155.00): order 2 (NY, local 08:00 Nov 1), order 3 (Kolkata, UTC Oct 31 19:00 is already local 00:30 Nov 1), order 5 (London, same day in both).

Now the pitfalls, run against the identical seed data:

-- Pitfall 1: bucket by the raw UTC date, ignore timezone entirely
SELECT COUNT(*) AS n_orders, SUM(amount_usd) AS revenue
FROM orders WHERE CAST(order_date AS DATE) = DATE '2025-11-01';
-- result: 4 orders, $380.00  (orders 1, 2, 4, 5)

-- Pitfall 2: apply ONE global fixed offset (UTC-5) to every user regardless of their actual zone
SELECT COUNT(*) AS n_orders, SUM(amount_usd) AS revenue
FROM orders WHERE CAST((order_date - INTERVAL 5 HOUR) AS DATE) = DATE '2025-11-01';
-- result: 3 orders, $280.00  (orders 2, 4, 5)

Correct: 3 orders, $155.00. Raw-UTC-date: 4 orders, $380.00 (wrongly includes order 1, which was really Oct 31 in New York, and wrongly includes order 4, which was really Nov 2 in Kolkata; wrongly excludes order 3). Global -5h offset: coincidentally also lands on 3 orders, but the WRONG 3: it wrongly excludes order 3 (Kolkata's true offset is +5:30, not -5) and wrongly includes order 4, while order 1 happens to still land on Oct 31 by coincidence. The row count matching the correct answer by accident is the most dangerous failure mode here: a count-only sanity check would not catch it, only the revenue total ($280 vs the correct $155) or a row-by-row diff would.

Trade-offs and pitfalls

• A users.timezone that is NULL or an invalid IANA string needs an explicit policy: LEFT JOIN plus COALESCE(u.timezone, 'UTC') with the fallback rows flagged in a separate is_timezone_assumed column, rather than silently dropping those users or silently mis-bucketing them as UTC without a flag.
• Per-row AT TIME ZONE conversion is not sargable (an index can't be used to search on it, because the column is wrapped in a function) against a plain index on order_date, so filtering a huge table to "around 2025-11-01" should first narrow with a cheap UTC range wide enough to cover every timezone's version of that local day (roughly order_date >= '2025-10-31' AND order_date < '2025-11-03' covers the full +14/-12 UTC offset range), then apply the exact per-user local-date predicate on that smaller set.
• The global-offset pitfall is really two bugs wearing one trenchcoat: cross-user (one offset cannot serve users in different zones) and cross-time (one offset cannot serve one zone across a DST boundary). Fixing only one of them (e.g. hardcoding "New York is always -5" outside DST season) still leaves the other live and waiting for the next March or November boundary.
• IANA tzdb data itself is updated periodically (governments change DST rules with short notice); pin and update the engine's tzdata/ICU version deliberately rather than assuming it is frozen forever.

Attribute each purchase's revenue by joining it to the buyer's first and last marketing touchpoint, then aggregate by channel under each model separately: a UNION ALL keeps first-touch and last-touch as two labeled result sets rather than blending them into one number. First-touch credits whichever channel started the relationship; last-touch credits whichever channel closed it. Neither model is more correct on its own: a stakeholder who owns awareness and top-of-funnel spend wants first-touch, while a stakeholder optimizing bottom-of-funnel channels (retargeting, paid search bidding) wants last-touch.

Approach

1. Rank a user's touchpoints by time, once ascending (to find the first touch) and once descending (to find the last touch), using ROW_NUMBER() partitioned by user.
2. Take the rn = 1 row from each ranking as that user's first-touch and last-touch channel.
3. LEFT JOIN purchases to each of those two lookups (not INNER JOIN), so a purchase from a user with zero recorded touchpoints still appears, labeled "unknown," instead of silently disappearing from the total.
4. UNION ALL the two attributed sets, tagging each with an attribution_model label, then GROUP BY model and channel.

Handling ties. If two touchpoints share the exact same touch_time for a user (duplicate event logging, same-second clickstream events), ordering by touch_time alone leaves ROW_NUMBER() unstable: which row lands on rn = 1 can differ between runs. Add a deterministic tiebreaker to the ORDER BY: a touchpoint primary key or ingestion sequence, not another text column like channel, since channel names just sort alphabetically and have nothing to do with which touchpoint actually happened first. The worked example below gives touchpoints a touchpoint_id surrogate key for exactly this purpose and includes a genuine cross-channel tie (user 6) to show it resolving deterministically.

When a stakeholder prefers which model. First-touch fits brand and awareness marketing ("which channel introduces us to buyers"); last-touch fits performance marketing and channels billed on last-click, like paid search bidding ("which channel closes the sale"). Neither handles split credit across a multi-touch path; a stakeholder who needs that is really asking for a fractional or time-decay model, which requires accumulating credit across the whole path, not just the min/max touch.

Worked example

Seed data and query (SQLite):

CREATE TABLE touchpoints (
  touchpoint_id INTEGER PRIMARY KEY,
  user_id     INTEGER,
  channel     TEXT,
  touch_time  TEXT
);

CREATE TABLE purchases (
  purchase_id INTEGER PRIMARY KEY,
  user_id     INTEGER,
  purchase_time TEXT,
  revenue     NUMERIC
);

INSERT INTO touchpoints (touchpoint_id, user_id, channel, touch_time) VALUES
  (1, 1, 'google',  '2026-01-01'),
  (2, 1, 'email',   '2026-01-03'),
  (3, 1, 'organic', '2026-01-05'),
  (4, 2, 'social',  '2026-01-02'),
  (5, 3, 'email',   '2026-01-01'),
  (6, 3, 'email',   '2026-01-01'),
  (7, 6, 'zeta',    '2026-01-01'),
  (8, 6, 'alpha',   '2026-01-01');

INSERT INTO purchases VALUES
  (101, 1, '2026-01-06', 100),
  (102, 2, '2026-01-03', 50),
  (103, 3, '2026-01-02', 75),
  (104, 4, '2026-01-02', 30),
  (105, 6, '2026-01-02', 60);

WITH ranked AS (
  SELECT
    user_id, channel, touch_time, touchpoint_id,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time ASC,  touchpoint_id ASC) AS rn_first,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time DESC, touchpoint_id ASC) AS rn_last
  FROM touchpoints
),
first_touch AS (SELECT user_id, channel FROM ranked WHERE rn_first = 1),
last_touch  AS (SELECT user_id, channel FROM ranked WHERE rn_last  = 1),
attributed AS (
  SELECT p.user_id, p.revenue, COALESCE(ft.channel, 'unknown') AS channel, 'first_touch' AS attribution_model
  FROM purchases p
  LEFT JOIN first_touch ft ON p.user_id = ft.user_id
  UNION ALL
  SELECT p.user_id, p.revenue, COALESCE(lt.channel, 'unknown') AS channel, 'last_touch' AS attribution_model
  FROM purchases p
  LEFT JOIN last_touch lt ON p.user_id = lt.user_id
)
SELECT attribution_model, channel, SUM(revenue) AS total_revenue
FROM attributed
GROUP BY attribution_model, channel
ORDER BY attribution_model, total_revenue DESC;

Result:

┌───────────────────┬─────────┬───────────────┐
│ attribution_model │ channel │ total_revenue │
├───────────────────┼─────────┼───────────────┤
│ first_touch       │ google  │ 100           │
│ first_touch       │ email   │ 75            │
│ first_touch       │ zeta    │ 60            │
│ first_touch       │ social  │ 50            │
│ first_touch       │ unknown │ 30            │
│ last_touch        │ organic │ 100           │
│ last_touch        │ email   │ 75            │
│ last_touch        │ zeta    │ 60            │
│ last_touch        │ social  │ 50            │
│ last_touch        │ unknown │ 30            │
└───────────────────┴─────────┴───────────────┘

User 1's three touches (google, then email, then organic) split their $100 purchase: google gets credit under first-touch, organic gets credit under last-touch. User 2 and user 3 have only one touch each, so both models agree for them. User 4 purchased with no logged touchpoint at all: both models correctly bucket that $30 as "unknown" rather than dropping it (an INNER JOIN would have silently dropped it) or guessing a channel.

User 6 is the real tiebreak case: two touchpoints logged the same second, in different channels ('zeta', touchpoint_id = 7, and 'alpha', touchpoint_id = 8). Alphabetically 'alpha' sorts before 'zeta', so a channel-based tiebreaker would hand the win to 'alpha'; but touchpoint_id is what actually orders them, and touchpoint 7 ('zeta') was ingested first, so 'zeta' wins the tie under both rankings here, exactly the deterministic-by-ingestion-order behavior the tiebreaker is supposed to produce, and the opposite of what alphabetizing on channel would have given.

Trade-offs & pitfalls

• Complexity: two window-function sorts over touchpoints (O(n log n) each) plus a UNION ALL that doubles the row count of purchases. For a very large purchases table, consider whether both breakdowns are actually needed in one query or would be cheaper as two simpler queries.
• Edge cases: users with zero touchpoints (handled by LEFT JOIN + COALESCE, not INNER JOIN); duplicate/tied touch_time values, resolved deterministically here by touchpoint_id, not by channel (demonstrated with user 6 above), since without a real tiebreaker "first touch" would silently change between runs; a touchpoint logged after the purchase itself, since this simple model doesn't check touch_time against purchase_time (add WHERE touch_time <= purchase_time before ranking if that ordering matters to the business).
• Common wrong turn: writing two separate queries, one per model, and never combining them, so the interviewer has to ask for a single result set. Also common: using INNER JOIN instead of LEFT JOIN, which silently drops purchases from untouched users and understates total attributed revenue without any error or warning.

Direct answer

Aggregate revenue by month, then use the LAG() window function to pull each month's prior-month total onto the same row. Percentage change is (current - previous) / previous, guarded against a missing or zero prior month with NULLIF.

Structured elaboration

• Aggregation step: date_trunc('month', created_at) buckets every order into its month. Keep a true month_start value (not just a display string) as the ORDER BY key for LAG, so window ordering is calendar-correct rather than dependent on string sort order.
• LAG(revenue) OVER (ORDER BY month_start) reaches back exactly one row, one month, given the prior aggregation, without needing a self-join.
• First month in the series: LAG() returns NULL, since there is no preceding row in the window. The CASE expression turns that into an explicit NULL for pct_change rather than an error or a misleading 0%. Coalescing the first month to 0% would falsely claim "no change" when there is actually nothing to compare against.

Worked example

Sample data (orders), tested in DuckDB 1.5:

WITH monthly AS (
  SELECT
    strftime(date_trunc('month', created_at), '%Y-%m') AS month,
    date_trunc('month', created_at) AS month_start,
    SUM(amount_cents) / 100.0 AS revenue
  FROM orders
  WHERE created_at >= DATE '2024-01-01'
    AND created_at <  DATE '2025-01-01'
  GROUP BY 1, 2
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month_start) AS prev_revenue,
  CASE
    WHEN LAG(revenue) OVER (ORDER BY month_start) IS NULL THEN NULL
    WHEN LAG(revenue) OVER (ORDER BY month_start) = 0 THEN NULL
    ELSE ROUND((revenue - LAG(revenue) OVER (ORDER BY month_start))
               / NULLIF(LAG(revenue) OVER (ORDER BY month_start), 0) * 100.0, 2)
  END AS pct_change
FROM monthly
ORDER BY month_start;

Result:

January totals $150 ($100 + $50), with no prior month, so pct_change is NULL. February totals $300 ($200 + $100), a 100% increase over January. March totals $150 ($90 + $60), a 50% decrease from February.

Trade-offs & pitfalls

• A single window function over an already-aggregated monthly table is cheap even at scale; the ORDER BY inside the window is the only sort involved.
• Displaying the first month: leaving pct_change as NULL (typically rendered blank in BI tools) is more honest than coalescing to 0. Decide with the requester which they actually want, and if 0 is chosen, label it clearly as "no prior period," not "0% growth."
• This query only emits a row for months that had at least one order. A month with zero orders is silently missing from the output rather than showing 0 revenue. If every calendar month needs to be represented, generate the month spine explicitly instead of relying on GROUP BY over the orders table.
• Currency handling: SUM(amount_cents) / 100.0 assumes a single currency and a cents-integer column. A real revenue table often needs currency conversion or a decimal amount column, don't silently sum mixed currencies.
• LAG() is standard SQL, supported in MySQL 8+, SQL Server, Postgres, DuckDB, Snowflake, and BigQuery, making this one of the more portable patterns in this set.

Direct answer

Before writing SQL, pin down what the stakeholder's sentence leaves ambiguous: does "steps A and B" require a specific order, what happens when signup_at is missing, and is the 14-day window inclusive of day 14. Once those are resolved into an explicit, written definition, the query is a straightforward comparison between each user's first-occurrence timestamps for A and B and their signup anchor.

Structured elaboration

Clarifying questions to ask, and the assumption I would pin if the stakeholder is unavailable:

1. Does order matter? Completing A then B, or just both regardless of order? Assumption if unstated: order does not matter, only "both events occurred," since the stakeholder said "completed steps A and B," not "completed A then B."
2. What if signup_at is missing? Assumption: treat the user as not activated, since a 14-day window can't be evaluated without an anchor, and separately flag these rows as a data-quality issue rather than silently dropping them from reporting.
3. Is "within 14 days" inclusive of day 14? Assumption: inclusive (<= signup_at + INTERVAL 14 DAY), matching how most product teams phrase day-based windows.
4. Do pre-signup events count? Assumption: no. Only step events at or after signup_at count toward activation; a step completed during a pre-signup trial should not count.

Writing these assumptions down (as SQL comments and in a shared doc) and getting them signed off is the actual skill being tested here, more than the SQL syntax itself.

Worked example

Sample data, tested in DuckDB 1.5:

users:

events:

WITH first_step AS (
  SELECT user_id, event_name, MIN(event_time) AS first_time
  FROM events
  WHERE event_name IN ('step_A', 'step_B')
  GROUP BY user_id, event_name
),
pivoted AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'step_A' THEN first_time END) AS a_time,
    MAX(CASE WHEN event_name = 'step_B' THEN first_time END) AS b_time
  FROM first_step
  GROUP BY user_id
)
SELECT
  u.user_id,
  u.signup_at,
  p.a_time,
  p.b_time,
  CASE
    WHEN u.signup_at IS NULL THEN FALSE
    WHEN p.a_time IS NULL OR p.b_time IS NULL THEN FALSE
    WHEN p.a_time < u.signup_at OR p.b_time < u.signup_at THEN FALSE
    WHEN GREATEST(p.a_time, p.b_time) <= u.signup_at + INTERVAL 14 DAY THEN TRUE
    ELSE FALSE
  END AS activated
FROM users u
LEFT JOIN pivoted p ON p.user_id = u.user_id
ORDER BY u.user_id;

Result:

User 2 shows the order-doesn't-matter assumption in action (B before A, still activated). User 4 shows the window boundary (B lands on day 20, outside 14 days). User 5 shows the missing-signup_at rule (not activated regardless of event activity).

Trade-offs & pitfalls

• A metric definition with unstated edge cases gets re-derived differently by every analyst who touches it. Writing the assumptions inline as SQL comments (or in a metric doc) is cheap insurance against silent drift.
• GREATEST(a_time, b_time) is the "both are done by this moment" anchor for the order-doesn't-matter assumption. If order turns out to matter (A must precede B), the check instead becomes a_time < b_time AND b_time <= signup_at + INTERVAL 14 DAY.
• Treating a missing signup_at as "not activated" is a defensible default, but if a large share of rows are missing it, that is a data-quality problem worth escalating before publishing the metric, not something to silently absorb into a lower activation rate.

Direct answer

Assign each user to a signup week (a fixed Monday-start bucket derived from signup_date), do the same week-bucketing to their events, then for each cohort week report the percentage of that cohort with at least one event in cohort-week-plus-N, for each N you care about. The two things that make this correct rather than approximately correct: bucketing both signup and event dates with the exact same week-start rule, and computing week offset as a count of week boundaries crossed, not a raw day difference divided by 7.

Structured elaboration

1. Cohort assignment: truncate each user's signup_date to the Monday that starts its week.
2. Event bucketing: apply the identical week-truncation rule to every event's date, so "week 1" for a cohort always means the same calendar week regardless of which day inside it the signup or event happened to land on.
3. Offset computation: week_offset = (event_week - signup_week) / 7 days, an integer.
4. Percent retained: for each cohort week and offset, COUNT(DISTINCT users active at that offset) / cohort_size.

Worked example (executed, sqlite3), including a real bug caught while verifying this answer

Seed data: cohort A signs up the week of 2025-06-02 (a Monday, 4 users), cohort B the week of 2025-06-09 (3 users), with deliberately uneven activity across the following two weeks.

CREATE TABLE users (user_id INTEGER PRIMARY KEY, signup_date TEXT);
INSERT INTO users VALUES
  (1,'2025-06-02'), (2,'2025-06-03'), (3,'2025-06-05'), (4,'2025-06-07'),
  (5,'2025-06-09'), (6,'2025-06-10'), (7,'2025-06-13');

CREATE TABLE events (user_id INTEGER, event_date TEXT);
INSERT INTO events VALUES
  (1,'2025-06-02'), (1,'2025-06-10'), (1,'2025-06-17'),
  (2,'2025-06-03'), (2,'2025-06-11'),
  (3,'2025-06-05'),
  (4,'2025-06-07'), (4,'2025-06-20'),
  (5,'2025-06-09'), (5,'2025-06-18'),
  (6,'2025-06-10'),
  (7,'2025-06-13'), (7,'2025-06-19');

First attempt at week-truncation used SQLite's built-in weekday N date modifier:

-- Looked correct, but had a real trap: 'weekday 1' only ADVANCES to the next
-- Monday; if the input date IS ALREADY Monday, it leaves it unchanged, so
-- stepping back '-7 days' afterward overshoots by a full extra week:
SELECT strftime('%w','2025-06-02') AS dow,                        -- 1 (Monday)
       date('2025-06-02','weekday 1')            AS naive_step1,   -- 2025-06-02 (unchanged)
       date('2025-06-02','weekday 1','-7 days')  AS naive_result;  -- 2025-05-26 (WRONG, a week early)

Running the full retention query with that formula put user 1 (who signed up on a Monday) into the wrong cohort week entirely, one week ahead of the other three users who signed up later that same week. That is caught here specifically because the seed data includes a signup that lands exactly on the week boundary, the case most naive week-truncation logic gets wrong.

Corrected week-start formula, using day-of-week arithmetic instead of the modifier:

-- strftime('%w', d) is 0=Sunday..6=Saturday; (%w + 6) % 7 = days since Monday
date(d, '-' || ((strftime('%w', d) + 6) % 7) || ' days')

Full corrected query:

WITH cohorts AS (
  SELECT user_id, date(signup_date, '-' || ((strftime('%w', signup_date) + 6) % 7) || ' days') AS signup_week
  FROM users
),
cohort_sizes AS (SELECT signup_week, COUNT(*) AS cohort_size FROM cohorts GROUP BY signup_week),
event_weeks AS (
  SELECT user_id, date(event_date, '-' || ((strftime('%w', event_date) + 6) % 7) || ' days') AS event_week
  FROM events
),
activity AS (
  SELECT c.signup_week, c.user_id,
    CAST((julianday(e.event_week) - julianday(c.signup_week)) / 7 AS INTEGER) AS week_offset
  FROM cohorts c JOIN event_weeks e ON e.user_id = c.user_id
  WHERE e.event_week >= c.signup_week
)
SELECT cs.signup_week, cs.cohort_size,
  ROUND(100.0*COUNT(DISTINCT CASE WHEN a.week_offset=0 THEN a.user_id END)/cs.cohort_size,1) AS pct_week0,
  ROUND(100.0*COUNT(DISTINCT CASE WHEN a.week_offset=1 THEN a.user_id END)/cs.cohort_size,1) AS pct_week1,
  ROUND(100.0*COUNT(DISTINCT CASE WHEN a.week_offset=2 THEN a.user_id END)/cs.cohort_size,1) AS pct_week2
FROM cohort_sizes cs LEFT JOIN activity a ON a.signup_week = cs.signup_week
GROUP BY cs.signup_week, cs.cohort_size ORDER BY cs.signup_week;

Result:

Every user has an event in their own signup week by construction, so week 0 is correctly 100.0% for both cohorts once the truncation bug is fixed. Cohort A: users 1 and 2 return in week 1 (2/4 = 50.0%); users 1 and 4 return in week 2 (2/4 = 50.0%). Cohort B: users 5 and 7 return in week 1 (2/3 = 66.7%); nobody returns in week 2 (0.0%), matching the seeded data exactly.

Trade-offs and pitfalls

• The week-truncation trap above is a genuine date-boundary off-by-one, the same failure family as a date_trunc cohort bug: it is invisible for signups in the middle of a week and only surfaces for a signup that lands exactly on the week-start day, so a test suite needs at least one such boundary case to catch it.
• Extending to day 1 through day 30: the same activity CTE pattern generalizes by computing offset in days instead of weeks (julianday(event_date) - julianday(signup_date)), but doing this efficiently for many offsets at once favors a single pass that buckets every event into its offset once, rather than a separate correlated subquery per offset day.
• Timezone-adjusted cohorts: if signup_date/event_date are UTC timestamps and users span timezones, the week boundary itself should be computed in each user's local time (the same per-user AT TIME ZONE pattern used for daily bucketing), otherwise a user near a week boundary can be misassigned by a few hours the same way a naive UTC-date bucket misassigns daily reports.
• Cohorts with very small cohort_size (a handful of users) produce noisy percentages, a single user churning swings the rate by double-digit points; report the raw cohort_size alongside the percentage so a reader does not over-trust a 50.0% built from 2 out of 4 users.