September 2010

-- #1 of each launch day
select distinct on (p.launch_date) p.slug, p.name, p.launch_date,
  (case when p.launch_date is not null and now() < ((p.launch_date::timestamp + interval '17 hours') at time zone 'utc') then 0 else coalesce(wv.n, 0) end) as votes_count, p.ai_score
from products p
left join lateral (
  select count(*)::int as n from votes v
  where v.product_id = p.id and v.created_at >= (p.launch_date::timestamp at time zone 'UTC')
    and v.created_at < ((p.launch_date + 1)::timestamp at time zone 'UTC')
) wv on true
where p.status = 'launched' and p.source not like 'seed%'
  and p.launch_date >= '2010-09-01' and p.launch_date < '2010-10-01'
order by p.launch_date desc,
  ((case when p.launch_date is not null and now() < ((p.launch_date::timestamp + interval '17 hours') at time zone 'utc') then 0 else coalesce(wv.n, 0) end) + coalesce(p.ai_score, 0)) desc, p.created_at asc;

-- #1 of each completed Wed..Tue week (run once per week, shown for 2010-W35)
select name, slug, (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) as votes_count, ai_score
from products p
where p.status = 'launched' and p.launch_date between '2010-09-01' and '2010-09-07'
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) + coalesce(ai_score, 0)) desc, p.launch_date desc
limit 1;

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

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

select pricing_model, count(*)::int as n
from products
where status = 'launched' and launch_date >= '2010-09-01' and launch_date < '2010-10-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 >= '2010-09-01' and launch_date < '2010-10-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 >= '2010-09-01' and p.launch_date < '2010-10-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 >= '2010-09-01' and launch_date < '2010-10-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 >= '2010-09-01T00:00:00.000Z' and created_at < '2010-10-01T00:00:00.000Z'
  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 >= '2010-09-01' and day < '2010-10-01'
group by bucket
order by n desc;

select *
from products p
where p.status = 'launched' and p.launch_date >= '2010-09-01' and p.launch_date < '2010-10-01'
  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;