November 2025

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

select
  (select count(*)::int from submissions
     where created_at >= '2025-11-01T00:00:00.000Z' and created_at < '2025-12-01T00:00:00.000Z' and source not like 'seed%') as total,
  (select count(*)::int from submissions
     where created_at >= '2025-11-01T00:00:00.000Z' and created_at < '2025-12-01T00:00:00.000Z' and source not like 'seed%'
       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 >= '2025-11-01T00:00:00.000Z' and created_at < '2025-12-01T00:00:00.000Z' and source not like 'seed%'
       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 >= '2025-11-01' and launch_date < '2025-12-01'
  and source not like 'seed%';

select pricing_model, count(*)::int as n
from products
where status = 'launched' and launch_date >= '2025-11-01' and launch_date < '2025-12-01'
  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 >= '2025-11-01' and launch_date < '2025-12-01'
  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 >= '2025-11-01' and p.launch_date < '2025-12-01'
  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 >= '2025-11-01' and launch_date < '2025-12-01'
    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 >= '2025-11-01T00:00:00.000Z' and created_at < '2025-12-01T00:00:00.000Z'
  and source not like 'seed%'
group by bucket
order by n desc;

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

select *
from products
where status = 'launched' and launch_date >= '2025-11-01' and launch_date < '2025-12-01'
  and source not like 'seed%'
order by (votes_count + ai_score) desc, launch_date desc
limit 10;