Event clock cohort alignment

SELECT 'event' AS kind,
  CASE
    WHEN e."createdAt" < TIMESTAMP '2026-04-14 00:00:00' THEN 'e_pre_mobile'
    WHEN e."createdAt" < TIMESTAMP '2026-05-06 14:53:38' THEN 'e_mobile_local_as_z'
    WHEN e."createdAt" < TIMESTAMP '2026-07-16 20:13:31' THEN 'e_gap_before_web_rfc'
    WHEN e."createdAt" < TIMESTAMP '2026-07-23 00:27:23' THEN 'e_web_z_pre_flex'
    WHEN e."createdAt" < TIMESTAMP '2026-08-16 20:08:29' THEN 'e_flex_instant_overlap'
    ELSE 'e_after_flex_wallclock'
  END AS window,
  COUNT(*) AS events,
  COUNT(e."startTime") AS with_start,
  COUNT(*) FILTER (WHERE fq.event_id IS NOT NULL) AS flex_linked
FROM "Event" e
LEFT JOIN flex_quotes fq ON fq.event_id = e.id
GROUP BY 2
UNION ALL
SELECT 'offer',
  CASE
    WHEN created_at < TIMESTAMP '2026-04-20 21:31:03' THEN 'o_early_z'
    WHEN created_at < TIMESTAMP '2026-07-15 00:00:19' THEN 'o_device_offset'
    ELSE 'o_late_z'
  END,
  COUNT(*),
  COUNT(start_time),
  COUNT(event_id)
FROM crew_offers
GROUP BY 2
UNION ALL
SELECT 'availability',
  CASE
    WHEN created_at < TIMESTAMP '2026-04-20 21:31:03' THEN 'a_early_z'
    WHEN created_at < TIMESTAMP '2026-07-15 00:00:19' THEN 'a_device_offset'
    ELSE 'a_late_z'
  END,
  COUNT(*),
  COUNT(start_time),
  0
FROM member_availability
GROUP BY 2
ORDER BY 1, 2;

WITH event_zones AS (
  SELECT e."organizationId" AS org_id,
         e.timezone,
         COUNT(*) AS n
    FROM "Event" e
   WHERE e.timezone IS NOT NULL
     AND e.timezone <> 'UTC'
   GROUP BY 1, 2
),
org_summary AS (
  SELECT o.id AS org_id,
         o.timezone AS org_timezone,
         (SELECT COUNT(*) FROM "Event" e WHERE e."organizationId" = o.id) AS events,
         (SELECT COUNT(*) FROM event_zones z WHERE z.org_id = o.id) AS distinct_iana,
         (SELECT z.timezone FROM event_zones z
           WHERE z.org_id = o.id ORDER BY z.n DESC, z.timezone LIMIT 1) AS mode_timezone
    FROM "Organization" o
)
SELECT
  CASE
    WHEN events = 0 THEN 'no_events'
    WHEN distinct_iana = 0 THEN 'events_but_no_iana'
    WHEN distinct_iana = 1 THEN 'single_iana'
    ELSE 'multi_iana'
  END AS org_class,
  COUNT(*) AS orgs,
  SUM(events) AS events
FROM org_summary
GROUP BY 1
ORDER BY events DESC;

SELECT o.id,
       (SELECT COUNT(*) FROM "Event" e WHERE e."organizationId" = o.id) AS events,
       ARRAY(
         SELECT z.timezone || '×' || z.n::text
           FROM (
             SELECT timezone, COUNT(*) AS n
               FROM "Event"
              WHERE "organizationId" = o.id
                AND timezone IS NOT NULL
              GROUP BY 1
           ) z
          ORDER BY z.n DESC
       ) AS zones
  FROM "Organization" o
 WHERE (
         SELECT COUNT(DISTINCT timezone)
           FROM "Event"
          WHERE "organizationId" = o.id
            AND timezone IS NOT NULL
            AND timezone <> 'UTC'
       ) > 1
 ORDER BY events DESC
 LIMIT 20;

SELECT
  e.timezone IS NULL OR e.timezone = 'UTC' AS missing_or_utc,
  e."startTime" IS NOT DISTINCT FROM fq.show_start_at AS start_still_instant,
  EXISTS (
    SELECT 1 FROM "Event" sib
     WHERE sib."organizationId" = e."organizationId"
       AND sib.id <> e.id
       AND sib.timezone IS NOT NULL
       AND sib.timezone <> 'UTC'
  ) AS org_has_sibling_iana,
  fq.metadata ? 'timezone' AS quote_has_metadata_tz,
  COUNT(*) AS n
FROM "Event" e
JOIN flex_quotes fq ON fq.event_id = e.id
WHERE e."createdAt" >= TIMESTAMP '2026-07-23 00:27:23'
  AND e."createdAt" < TIMESTAMP '2026-08-16 20:08:29'
  AND fq.show_start_at IS NOT NULL
GROUP BY 1, 2, 3, 4
ORDER BY n DESC;

WITH org_mode AS (
  SELECT "organizationId" AS org_id, timezone, COUNT(*) AS n,
         ROW_NUMBER() OVER (PARTITION BY "organizationId" ORDER BY COUNT(*) DESC, timezone) AS rn
    FROM "Event"
   WHERE timezone IS NOT NULL AND timezone <> 'UTC'
   GROUP BY 1, 2
),
inferred AS (
  SELECT o.id,
         COALESCE(e.timezone, m.timezone) AS tz,
         o.date AS offer_day,
         o.start_time,
         CASE
           WHEN COALESCE(e.timezone, m.timezone) IS NULL THEN NULL
           ELSE ((o.start_time AT TIME ZONE 'UTC')
                 AT TIME ZONE COALESCE(e.timezone, m.timezone))
         END AS local_ts
    FROM crew_offers o
    LEFT JOIN "Event" e ON e.id = o.event_id
    LEFT JOIN org_mode m ON m.org_id = o.organization_id AND m.rn = 1
   WHERE o.created_at >= TIMESTAMP '2026-04-20 21:31:03'
     AND o.created_at < TIMESTAMP '2026-07-15 00:00:19'
     AND o.start_time IS NOT NULL
)
SELECT
  tz IS NULL AS no_inferred_tz,
  (start_time AT TIME ZONE 'UTC')::date IS NOT DISTINCT FROM offer_day AS utc_day_matches_offer,
  local_ts::date IS NOT DISTINCT FROM offer_day AS local_day_matches_offer,
  COUNT(*) AS n
FROM inferred
GROUP BY 1, 2, 3
ORDER BY n DESC;

WITH org_mode AS (
  SELECT "organizationId" AS org_id, timezone, COUNT(*) AS n,
         ROW_NUMBER() OVER (PARTITION BY "organizationId" ORDER BY COUNT(*) DESC, timezone) AS rn
    FROM "Event"
   WHERE timezone IS NOT NULL AND timezone <> 'UTC'
   GROUP BY 1, 2
)
SELECT
  EXTRACT(HOUR FROM ((o.start_time AT TIME ZONE 'UTC')
    AT TIME ZONE COALESCE(e.timezone, m.timezone)))::int AS local_hour,
  COUNT(*) AS n
FROM crew_offers o
LEFT JOIN "Event" e ON e.id = o.event_id
LEFT JOIN org_mode m ON m.org_id = o.organization_id AND m.rn = 1
WHERE o.created_at >= TIMESTAMP '2026-04-20 21:31:03'
  AND o.created_at < TIMESTAMP '2026-07-15 00:00:19'
  AND o.start_time IS NOT NULL
  AND COALESCE(e.timezone, m.timezone) IS NOT NULL
  AND (o.start_time AT TIME ZONE 'UTC')::date IS DISTINCT FROM o.date
  AND ((o.start_time AT TIME ZONE 'UTC')
       AT TIME ZONE COALESCE(e.timezone, m.timezone))::date
      IS NOT DISTINCT FROM o.date
GROUP BY 1
ORDER BY 1;

SELECT
  fq.event_id IS NOT NULL AS flex_linked,
  CASE
    WHEN e."eventDate"::time = TIME '00:00:00' THEN 'midnight'
    WHEN e."eventDate"::time = TIME '12:00:00' THEN 'noon'
    ELSE 'other'
  END AS clock,
  COUNT(*) AS n
FROM "Event" e
LEFT JOIN flex_quotes fq ON fq.event_id = e.id
GROUP BY 1, 2
ORDER BY 1, 2;

SELECT COALESCE(fq.metadata->>'timezone', '<absent>') AS meta_tz,
       COUNT(*) AS n
  FROM flex_quotes fq
  JOIN "Event" e ON e.id = fq.event_id
 WHERE e."createdAt" >= TIMESTAMP '2026-07-23 00:27:23'
   AND e."createdAt" < TIMESTAMP '2026-08-16 20:08:29'
 GROUP BY 1
 ORDER BY n DESC;

SELECT 'offer_offset' AS kind,
       EXTRACT(HOUR FROM o.start_time)::int AS utc_hour,
       COUNT(*) AS n
  FROM crew_offers o
 WHERE o.created_at >= TIMESTAMP '2026-04-20 21:31:03'
   AND o.created_at < TIMESTAMP '2026-07-15 00:00:19'
   AND o.start_time IS NOT NULL
 GROUP BY 2
UNION ALL
SELECT 'avail_offset',
       EXTRACT(HOUR FROM a.start_time)::int,
       COUNT(*)
  FROM member_availability a
 WHERE a.created_at >= TIMESTAMP '2026-04-20 21:31:03'
   AND a.created_at < TIMESTAMP '2026-07-15 00:00:19'
 GROUP BY 2
UNION ALL
SELECT 'event_pre_mobile',
       EXTRACT(HOUR FROM e."startTime")::int,
       COUNT(*)
  FROM "Event" e
 WHERE e."createdAt" < TIMESTAMP '2026-04-14 00:00:00'
   AND e."startTime" IS NOT NULL
 GROUP BY 2
ORDER BY 1, 2;

-- Session-sensitive — do not use to decide a rewrite.
SELECT
  (a.start_time AT TIME ZONE 'UTC')::date - a.date AS session_days_from_avail,
  COUNT(*) AS n
FROM member_availability a
WHERE a.created_at >= TIMESTAMP '2026-04-20 21:31:03'
  AND a.created_at < TIMESTAMP '2026-07-15 00:00:19'
  AND a.start_time IS NOT NULL
GROUP BY 1
ORDER BY n DESC;

SHOW TimeZone;

SELECT
  a.start_time::date - a.date AS utc_days_from_avail,
  COUNT(*) AS n
FROM member_availability a
WHERE a.created_at >= TIMESTAMP '2026-04-20 21:31:03'
  AND a.created_at < TIMESTAMP '2026-07-15 00:00:19'
GROUP BY 1
ORDER BY n DESC;

SELECT
  EXTRACT(HOUR FROM a.start_time)::int AS start_h,
  EXTRACT(HOUR FROM a.end_time)::int AS end_h,
  a.end_time::date - a.start_time::date AS end_utc_days_from_start,
  a.start_time::date - a.date AS start_utc_days_from_avail,
  COUNT(*) AS n
FROM member_availability a
WHERE a.created_at >= TIMESTAMP '2026-04-20 21:31:03'
  AND a.created_at < TIMESTAMP '2026-07-15 00:00:19'
  AND EXTRACT(HOUR FROM a.start_time) <> 0
GROUP BY 1, 2, 3, 4
ORDER BY n DESC;

SELECT fq.event_id IS NOT NULL AS flex_linked,
       EXTRACT(HOUR FROM e."eventDate")::int AS h,
       EXTRACT(MINUTE FROM e."eventDate")::int AS m,
       COUNT(*) AS n
  FROM "Event" e
  LEFT JOIN flex_quotes fq ON fq.event_id = e.id
 WHERE e."eventDate"::time <> TIME '00:00:00'
   AND e."eventDate"::time <> TIME '12:00:00'
 GROUP BY 1, 2, 3
 ORDER BY 1, n DESC;

SELECT
  CASE
    WHEN e."createdAt" < TIMESTAMP '2026-07-16 20:13:31' THEN 'e_gap_before_web_rfc'
    WHEN e."createdAt" < TIMESTAMP '2026-07-23 00:27:23' THEN 'e_web_z_pre_flex'
    ELSE 'other'
  END AS window,
  e.timezone IS NULL OR e.timezone = 'UTC' AS missing_or_utc,
  e."startTime" IS NOT DISTINCT FROM fq.show_start_at AS start_still_instant,
  e."endTime" IS NOT DISTINCT FROM fq.load_out_at AS end_still_instant,
  NULLIF(fq.metadata->>'timezone', 'UTC') IS NOT NULL AS quote_has_real_meta_tz,
  (
    e.latitude IS NOT NULL AND e.longitude IS NOT NULL
    AND NOT (e.latitude = 0 AND e.longitude = 0)
  ) AS event_has_coords,
  (
    v.latitude IS NOT NULL AND v.longitude IS NOT NULL
    AND NOT (v.latitude = 0 AND v.longitude = 0)
  ) AS venue_has_coords,
  COUNT(*) AS n
FROM "Event" e
JOIN flex_quotes fq ON fq.event_id = e.id
LEFT JOIN "Venue" v ON v.id = e.venue_id
WHERE e."createdAt" < TIMESTAMP '2026-07-23 00:27:23'
  AND fq.show_start_at IS NOT NULL
GROUP BY 1, 2, 3, 4, 5, 6, 7
ORDER BY n DESC;

SELECT
  EXTRACT(HOUR FROM e."eventDate")::int AS event_date_h,
  EXTRACT(MINUTE FROM e."eventDate")::int AS event_date_m,
  e."eventDate"::date IS NOT DISTINCT FROM fq.show_start_at::date AS utc_day_matches_show,
  CASE
    WHEN NULLIF(fq.metadata->>'timezone', 'UTC') IS NULL THEN NULL
    ELSE ((fq.show_start_at AT TIME ZONE 'UTC')
          AT TIME ZONE NULLIF(fq.metadata->>'timezone', 'UTC'))::date
         IS NOT DISTINCT FROM e."eventDate"::date
  END AS local_show_day_matches_event_date,
  e."startTime" IS NOT DISTINCT FROM fq.show_start_at AS start_still_instant,
  COUNT(*) AS n
FROM "Event" e
JOIN flex_quotes fq ON fq.event_id = e.id
WHERE e."eventDate"::time <> TIME '00:00:00'
  AND e."eventDate"::time <> TIME '12:00:00'
GROUP BY 1, 2, 3, 4, 5
ORDER BY n DESC;

SELECT
  EXTRACT(HOUR FROM e."eventDate")::int AS event_date_h,
  EXTRACT(HOUR FROM e."startTime")::int AS start_h,
  e."startTime" IS NOT NULL AS has_start,
  e."eventDate"::date IS NOT DISTINCT FROM e."startTime"::date AS same_utc_day_as_start,
  (
    e.latitude IS NOT NULL AND e.longitude IS NOT NULL
    AND NOT (e.latitude = 0 AND e.longitude = 0)
  ) AS event_has_coords,
  e.venue_id IS NOT NULL AS has_venue,
  COUNT(*) AS n
FROM "Event" e
LEFT JOIN flex_quotes fq ON fq.event_id = e.id
WHERE fq.event_id IS NULL
  AND e."eventDate"::time <> TIME '00:00:00'
  AND e."eventDate"::time <> TIME '12:00:00'
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY n DESC;

SELECT
  CASE
    WHEN e."createdAt" < TIMESTAMP '2026-07-16 20:13:31' THEN 'e_gap_before_web_rfc'
    WHEN e."createdAt" < TIMESTAMP '2026-07-23 00:27:23' THEN 'e_web_z_pre_flex'
    WHEN e."createdAt" < TIMESTAMP '2026-08-16 20:08:29' THEN 'e_flex_instant_overlap'
    ELSE 'other'
  END AS window,
  EXTRACT(HOUR FROM e."startTime")::int AS start_h,
  COUNT(*) AS n
FROM "Event" e
LEFT JOIN flex_quotes fq ON fq.event_id = e.id
WHERE e."startTime" IS NOT NULL
  AND fq.event_id IS NULL
  AND e."createdAt" >= TIMESTAMP '2026-05-06 14:53:38'
  AND e."createdAt" < TIMESTAMP '2026-08-16 20:08:29'
GROUP BY 1, 2
ORDER BY 1, 2;

SELECT
  CASE
    WHEN i.created_at < TIMESTAMP '2026-06-16 00:00:00' THEN 'ros_before_offset'
    WHEN i.created_at < TIMESTAMP '2026-07-15 00:00:19' THEN 'ros_device_offset'
    ELSE 'ros_late_z'
  END AS window,
  COUNT(*) AS items,
  COUNT(i.start_time) AS with_start
FROM run_of_show_items i
GROUP BY 1
ORDER BY 1;

SELECT
  EXTRACT(HOUR FROM i.start_time)::int AS start_h,
  EXTRACT(HOUR FROM i.end_time)::int AS end_h,
  i.end_time::date - i.start_time::date AS end_utc_days_from_start,
  i.start_time::date - e."eventDate"::date AS start_utc_days_from_event,
  COUNT(*) AS n
FROM run_of_show_items i
JOIN "Event" e ON e.id = i.event_id
WHERE i.created_at >= TIMESTAMP '2026-06-16 00:00:00'
  AND i.created_at < TIMESTAMP '2026-07-15 00:00:19'
  AND i.start_time IS NOT NULL
GROUP BY 1, 2, 3, 4
ORDER BY n DESC;

SELECT
  NULLIF(fq.metadata->>'timezone', 'UTC') IS NOT NULL AS has_meta_tz,
  (
    e.latitude IS NOT NULL AND e.longitude IS NOT NULL
    AND NOT (e.latitude = 0 AND e.longitude = 0)
  ) AS event_has_coords,
  (
    v.latitude IS NOT NULL AND v.longitude IS NOT NULL
    AND NOT (v.latitude = 0 AND v.longitude = 0)
  ) AS venue_has_coords,
  NULLIF(o.timezone, 'UTC') IS NOT NULL AS org_has_tz,
  COUNT(*) AS n
FROM "Event" e
JOIN flex_quotes fq ON fq.event_id = e.id
JOIN "Organization" o ON o.id = e."organizationId"
LEFT JOIN "Venue" v ON v.id = e.venue_id
WHERE e."createdAt" < TIMESTAMP '2026-07-23 00:27:23'
  AND e."startTime" IS NOT DISTINCT FROM fq.show_start_at
GROUP BY 1, 2, 3, 4
ORDER BY n DESC;

SELECT
  CASE
    WHEN upper(COALESCE(v.address->>'state', '')) IN (
      'IL', 'ILLINOIS', 'TX', 'TEXAS', 'WI', 'WISCONSIN', 'MN', 'MINNESOTA',
      'MO', 'MISSOURI', 'IA', 'IOWA', 'AR', 'ARKANSAS', 'LA', 'LOUISIANA',
      'OK', 'OKLAHOMA', 'KS', 'KANSAS', 'NE', 'NEBRASKA', 'ND', 'NORTH DAKOTA',
      'SD', 'SOUTH DAKOTA', 'AL', 'ALABAMA', 'MS', 'MISSISSIPPI', 'TN', 'TENNESSEE'
    ) THEN 'centralish'
    WHEN upper(COALESCE(v.address->>'state', '')) IN (
      'NY', 'NEW YORK', 'NJ', 'NEW JERSEY', 'PA', 'PENNSYLVANIA', 'FL', 'FLORIDA',
      'GA', 'GEORGIA', 'NC', 'NORTH CAROLINA', 'SC', 'SOUTH CAROLINA',
      'VA', 'VIRGINIA', 'MD', 'MARYLAND', 'DC', 'MA', 'MASSACHUSETTS',
      'CT', 'CONNECTICUT', 'OH', 'OHIO', 'MI', 'MICHIGAN', 'IN', 'INDIANA'
    ) THEN 'easternish'
    WHEN upper(COALESCE(v.address->>'state', '')) IN (
      'CA', 'CALIFORNIA', 'WA', 'WASHINGTON', 'OR', 'OREGON', 'NV', 'NEVADA'
    ) THEN 'pacificish'
    WHEN upper(COALESCE(v.address->>'state', '')) IN (
      'CO', 'COLORADO', 'AZ', 'ARIZONA', 'UT', 'UTAH', 'NM', 'NEW MEXICO',
      'MT', 'MONTANA', 'WY', 'WYOMING', 'ID', 'IDAHO'
    ) THEN 'mountainish'
    WHEN v.id IS NULL THEN 'no_venue'
    WHEN v.address->>'state' IS NULL OR v.address->>'state' = '' THEN 'venue_no_state'
    ELSE 'other_or_non_us'
  END AS state_band,
  COUNT(*) AS events,
  COUNT(*) FILTER (
    WHERE e.timezone IS NULL OR e.timezone = 'UTC'
  ) AS missing_event_tz
FROM "Event" e
LEFT JOIN "Venue" v ON v.id = e.venue_id
GROUP BY 1
ORDER BY events DESC;

SELECT DISTINCT
  v.name AS venue_name,
  v.address->>'city' AS city,
  v.address->>'state' AS state,
  (
    v.latitude IS NOT NULL AND v.longitude IS NOT NULL
    AND NOT (v.latitude = 0 AND v.longitude = 0)
  ) AS venue_has_coords,
  NULLIF(fq.metadata->>'timezone', 'UTC') AS meta_tz
FROM "Event" e
JOIN flex_quotes fq ON fq.event_id = e.id
LEFT JOIN "Venue" v ON v.id = e.venue_id
WHERE e."createdAt" < TIMESTAMP '2026-07-23 00:27:23'
  AND e."startTime" IS NOT DISTINCT FROM fq.show_start_at
ORDER BY 3, 2, 1;

WITH multi AS (
  SELECT e."organizationId" AS org_id
    FROM "Event" e
   WHERE e.timezone IS NOT NULL AND e.timezone <> 'UTC'
   GROUP BY 1
  HAVING COUNT(DISTINCT e.timezone) > 1
)
SELECT DISTINCT
  e.timezone AS event_tz,
  v.name AS venue_name,
  v.address->>'city' AS city,
  v.address->>'state' AS state
FROM "Event" e
JOIN multi m ON m.org_id = e."organizationId"
LEFT JOIN "Venue" v ON v.id = e.venue_id
ORDER BY 1, 4, 3, 2;

SELECT DISTINCT
  EXTRACT(HOUR FROM e."eventDate")::int AS event_date_h,
  v.address->>'state' AS state,
  v.name AS venue_name
FROM "Event" e
LEFT JOIN flex_quotes fq ON fq.event_id = e.id
LEFT JOIN "Venue" v ON v.id = e.venue_id
WHERE fq.event_id IS NULL
  AND e."eventDate"::time <> TIME '00:00:00'
  AND e."eventDate"::time <> TIME '12:00:00'
ORDER BY 1, 2, 3;