November 2026 (month to date)

select source, count(*)::int as n
from submissions
where created_at >= '2026-11-01T00:00:00.000Z' and created_at < '2026-11-01T13:35:41.219Z'
  and source not like 'seed%' and not is_internal
group by source
order by source;

select
  (select count(*)::int from submissions
     where created_at >= '2026-11-01T00:00:00.000Z' and created_at < '2026-11-01T13:35:41.219Z' and source not like 'seed%' and not is_internal) as total,
  (select count(*)::int from submissions
     where created_at >= '2026-11-01T00:00:00.000Z' and created_at < '2026-11-01T13:35:41.219Z' and source not like 'seed%' and not is_internal
       and verified_at is not null) as verified,
  (select percentile_cont(0.5) within group (order by extract(epoch from (verified_at - created_at)) / 3600.0)
     from submissions
     where created_at >= '2026-11-01T00:00:00.000Z' and created_at < '2026-11-01T13:35:41.219Z' and source not like 'seed%' and not is_internal
       and verified_at is not null) as median_hours_to_verify;

select
  count(*)::int as total,
  count(*) filter (where self_scheduled)::int as self_scheduled
from products
where status = 'launched' and launch_date >= '2026-11-01' and launch_date < '2026-11-02'
  and source not like 'seed%';

select pricing_model, count(*)::int as n
from products
where status = 'launched' and launch_date >= '2026-11-01' and launch_date < '2026-11-02'
  and source not like 'seed%'
group by pricing_model
order by n desc, pricing_model;

select count(*)::int as priced,
       percentile_cont(0.5) within group (order by pricing_amount_usd) as median_price_usd
from products
where status = 'launched' and launch_date >= '2026-11-01' and launch_date < '2026-11-02'
  and source not like 'seed%'
  and pricing_amount_usd is not null;

select c.slug, c.name, count(*)::int as n
from product_categories pc
join products p on p.id = pc.product_id
join categories c on c.slug = pc.category_slug
where pc.is_primary
  and p.status = 'launched' and p.launch_date >= '2026-11-01' and p.launch_date < '2026-11-02'
  and p.source not like 'seed%'
group by c.slug, c.name
order by n desc, c.name
limit 8;

with launched as (
  select is_indexable,
    case when btrim(coalesce(description, '')) = '' then 0
         else array_length(regexp_split_to_array(btrim(description), '\s+'), 1)
    end as words
  from products
  where status = 'launched' and launch_date >= '2026-11-01' and launch_date < '2026-11-02'
    and source not like 'seed%'
)
select
  count(*)::int as total,
  count(*) filter (where is_indexable)::int as indexable,
  count(*) filter (where words < 100)::int as under_100_words,
  percentile_cont(0.5) within group (order by words) as median_words
from launched;

select
  case
    when referrer_source = 'chatgpt.com' then 'chatgpt.com'
    when referrer_source = 'mcp' then 'mcp'
    when referrer_source is null then 'unattributed'
    else 'other'
  end as bucket,
  count(*)::int as n
from submissions
where created_at >= '2026-11-01T00:00:00.000Z' and created_at < '2026-11-01T13:35:41.219Z'
  and source not like 'seed%' and not is_internal
group by bucket
order by n desc;

select bucket, sum(n)::int as n
from listing_requests
where day >= '2026-11-01' and day < '2026-11-02'
group by bucket
order by n desc;

select *
from products p
where p.status = 'launched' and p.launch_date >= '2026-11-01' and p.launch_date < '2026-11-02'
  and p.source not like 'seed%'
order by ((case when p.launch_date is not null and now() < ((p.launch_date::timestamp + interval '17 hours') at time zone 'utc') then 0 else p.votes_count end) + p.ai_score) desc, p.launch_date desc
limit 10;