What is average check size (average ticket)?

WITH closed AS (
  SELECT
    c.check_id,
    c.location_id,
    c.channel,
    -- Dayparts are wall-clock concepts, so bucket by the venue's LOCAL
    -- time. If closed_at is stored in UTC, convert it with the location's
    -- timezone first; bucketing raw UTC hours misdates every venue that
    -- isn't on UTC. (Drop the conversion if closed_at is already local.)
    (c.closed_at AT TIME ZONE 'UTC' AT TIME ZONE l.timezone)
      AS local_closed_at,
    c.guest_count,
    -- Convention, stated once and applied everywhere: net sales is
    -- post-discount and post-comp, pre-tax, pre-tip, pre-service-charge.
    c.gross_sales - c.discount_amount - c.comp_amount AS net_sales,
    c.gross_sales
  FROM pos_checks c
  JOIN pos_locations l ON l.location_id = c.location_id
  WHERE c.status = 'closed'
    AND c.voided_at IS NULL
    AND c.closed_at >= CURRENT_DATE - INTERVAL '30 days'
), parted AS (
  SELECT
    closed.*,
    -- Late night spans 22:00–04:59 so a post-midnight close isn't
    -- misfiled as breakfast — the classic bug for venues open past 12.
    CASE
      WHEN EXTRACT(hour FROM local_closed_at) BETWEEN 5 AND 10
        THEN 'breakfast'
      WHEN EXTRACT(hour FROM local_closed_at) BETWEEN 11 AND 15
        THEN 'lunch'
      WHEN EXTRACT(hour FROM local_closed_at) BETWEEN 16 AND 21
        THEN 'dinner'
      ELSE 'late night'
    END AS daypart
  FROM closed
)
SELECT
  daypart,
  channel,
  COUNT(*) AS checks,
  ROUND(AVG(net_sales), 2) AS avg_check,
  ROUND(
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY net_sales)::numeric, 2
  ) AS median_check,
  -- p90 next to the mean shows when large party checks are doing the
  -- work; a mean far above the median is a distribution, not a trend.
  ROUND(
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY net_sales)::numeric, 2
  ) AS p90_check,
  -- True per-guest figure: total sales over total guests. Averaging the
  -- per-check ratio would weight a 2-top the same as a 20-top.
  ROUND(SUM(net_sales) / NULLIF(SUM(guest_count), 0), 2) AS sales_per_guest,
  ROUND(
    100.0 * SUM(gross_sales - net_sales) / NULLIF(SUM(gross_sales), 0), 1
  ) AS discount_and_comp_pct,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE guest_count IS NULL)
    / NULLIF(COUNT(*), 0), 1
  ) AS pct_missing_guest_count
FROM parted
GROUP BY 1, 2
ORDER BY 1, 2;