{"queries":[{"id":"build_info","compiledQueryString":"select build_timestamp_utc from as24ch.metadata","inputQueryString":"select build_timestamp_utc from as24ch.metadata","compiled":false,"inline":true},{"id":"brand_list","compiledQueryString":"select distinct brand from as24ch.listings where status = 'active' and brand is not null order by brand","inputQueryString":"select distinct brand from as24ch.listings where status = 'active' and brand is not null order by brand","compiled":false,"inline":true},{"id":"model_list","compiledQueryString":"select distinct model from as24ch.listings\nwhere status = 'active' and model is not null\n and ('${inputs.brand.value}' = '' or brand = '${inputs.brand.value}')\norder by model","inputQueryString":"select distinct model from as24ch.listings\nwhere status = 'active' and model is not null\n and ('${inputs.brand.value}' = '' or brand = '${inputs.brand.value}')\norder by model","compiled":false,"inline":true},{"id":"seller_base_all","compiledQueryString":"with fl as (\n select l.seller_internal_id::varchar as seller_internal_id,\n COALESCE(sn.seller_name, l.seller_name) as seller_name,\n l.current_price, l.mileage_km, l.year\n from as24ch.listings l\n left join as24ch.seller_names sn\n on l.seller_internal_id::varchar = sn.seller_internal_id\n where l.status = 'active'\n and ('${inputs.brand.value}' = '' or l.brand = '${inputs.brand.value}')\n and ('${inputs.model.value}' = '' or '${inputs.brand.value}' = '' or l.model = '${inputs.model.value}')\n and (l.year is null or l.year between COALESCE(NULLIF('${inputs.year_min}', ''), '2016')::int and COALESCE(NULLIF('${inputs.year_max}', ''), '2026')::int)\n and (l.mileage_km is null or l.mileage_km between COALESCE(NULLIF('${inputs.min_km}', ''), '0')::int and COALESCE(NULLIF('${inputs.max_km}', ''), '300000')::int)\n)\nselect\n seller_internal_id,\n max(seller_name) as seller_name,\n count(*) as stock,\n avg(current_price)::int as avg_price,\n median(current_price)::int as median_price,\n avg(mileage_km)::int as avg_mileage,\n avg(year)::numeric(5,1) as avg_year\nfrom fl\ngroup by seller_internal_id\norder by stock desc","inputQueryString":"with fl as (\n select l.seller_internal_id::varchar as seller_internal_id,\n COALESCE(sn.seller_name, l.seller_name) as seller_name,\n l.current_price, l.mileage_km, l.year\n from as24ch.listings l\n left join as24ch.seller_names sn\n on l.seller_internal_id::varchar = sn.seller_internal_id\n where l.status = 'active'\n and ('${inputs.brand.value}' = '' or l.brand = '${inputs.brand.value}')\n and ('${inputs.model.value}' = '' or '${inputs.brand.value}' = '' or l.model = '${inputs.model.value}')\n and (l.year is null or l.year between COALESCE(NULLIF('${inputs.year_min}', ''), '2016')::int and COALESCE(NULLIF('${inputs.year_max}', ''), '2026')::int)\n and (l.mileage_km is null or l.mileage_km between COALESCE(NULLIF('${inputs.min_km}', ''), '0')::int and COALESCE(NULLIF('${inputs.max_km}', ''), '300000')::int)\n)\nselect\n seller_internal_id,\n max(seller_name) as seller_name,\n count(*) as stock,\n avg(current_price)::int as avg_price,\n median(current_price)::int as median_price,\n avg(mileage_km)::int as avg_mileage,\n avg(year)::numeric(5,1) as avg_year\nfrom fl\ngroup by seller_internal_id\norder by stock desc","compiled":false,"inline":true},{"id":"seller_base","compiledQueryString":"with fl as (\n select l.seller_internal_id::varchar as seller_internal_id,\n COALESCE(sn.seller_name, l.seller_name) as seller_name,\n l.current_price, l.mileage_km, l.year\n from as24ch.listings l\n left join as24ch.seller_names sn\n on l.seller_internal_id::varchar = sn.seller_internal_id\n where l.status = 'active'\n and ('${inputs.brand.value}' = '' or l.brand = '${inputs.brand.value}')\n and ('${inputs.model.value}' = '' or '${inputs.brand.value}' = '' or l.model = '${inputs.model.value}')\n and (l.year is null or l.year between COALESCE(NULLIF('${inputs.year_min}', ''), '2016')::int and COALESCE(NULLIF('${inputs.year_max}', ''), '2026')::int)\n and (l.mileage_km is null or l.mileage_km between COALESCE(NULLIF('${inputs.min_km}', ''), '0')::int and COALESCE(NULLIF('${inputs.max_km}', ''), '300000')::int)\n)\nselect\n seller_internal_id,\n max(seller_name) as seller_name,\n count(*) as stock,\n avg(current_price)::int as avg_price,\n median(current_price)::int as median_price,\n avg(mileage_km)::int as avg_mileage,\n avg(year)::numeric(5,1) as avg_year\nfrom fl\ngroup by seller_internal_id\nhaving count(*) >= cast('${inputs.min_stock.value}' as integer)\norder by stock desc\nlimit 500","inputQueryString":"with fl as (\n select l.seller_internal_id::varchar as seller_internal_id,\n COALESCE(sn.seller_name, l.seller_name) as seller_name,\n l.current_price, l.mileage_km, l.year\n from as24ch.listings l\n left join as24ch.seller_names sn\n on l.seller_internal_id::varchar = sn.seller_internal_id\n where l.status = 'active'\n and ('${inputs.brand.value}' = '' or l.brand = '${inputs.brand.value}')\n and ('${inputs.model.value}' = '' or '${inputs.brand.value}' = '' or l.model = '${inputs.model.value}')\n and (l.year is null or l.year between COALESCE(NULLIF('${inputs.year_min}', ''), '2016')::int and COALESCE(NULLIF('${inputs.year_max}', ''), '2026')::int)\n and (l.mileage_km is null or l.mileage_km between COALESCE(NULLIF('${inputs.min_km}', ''), '0')::int and COALESCE(NULLIF('${inputs.max_km}', ''), '300000')::int)\n)\nselect\n seller_internal_id,\n max(seller_name) as seller_name,\n count(*) as stock,\n avg(current_price)::int as avg_price,\n median(current_price)::int as median_price,\n avg(mileage_km)::int as avg_mileage,\n avg(year)::numeric(5,1) as avg_year\nfrom fl\ngroup by seller_internal_id\nhaving count(*) >= cast('${inputs.min_stock.value}' as integer)\norder by stock desc\nlimit 500","compiled":false,"inline":true},{"id":"kpis","compiledQueryString":"select\n count(*) filter (where stock >= 5) as pro_sellers,\n count(*) filter (where stock < 5) as private_sellers,\n sum(stock) filter (where stock >= 5) as pro_listings,\n sum(stock) filter (where stock < 5) as private_listings,\n round(\n sum(stock) filter (where stock >= 5) * 1.0\n / nullif(sum(stock), 0), 3\n ) as pro_share_pct\nfrom ((with fl as (\n select l.seller_internal_id::varchar as seller_internal_id,\n COALESCE(sn.seller_name, l.seller_name) as seller_name,\n l.current_price, l.mileage_km, l.year\n from as24ch.listings l\n left join as24ch.seller_names sn\n on l.seller_internal_id::varchar = sn.seller_internal_id\n where l.status = 'active'\n and ('${inputs.brand.value}' = '' or l.brand = '${inputs.brand.value}')\n and ('${inputs.model.value}' = '' or '${inputs.brand.value}' = '' or l.model = '${inputs.model.value}')\n and (l.year is null or l.year between COALESCE(NULLIF('${inputs.year_min}', ''), '2016')::int and COALESCE(NULLIF('${inputs.year_max}', ''), '2026')::int)\n and (l.mileage_km is null or l.mileage_km between COALESCE(NULLIF('${inputs.min_km}', ''), '0')::int and COALESCE(NULLIF('${inputs.max_km}', ''), '300000')::int)\n)\nselect\n seller_internal_id,\n max(seller_name) as seller_name,\n count(*) as stock,\n avg(current_price)::int as avg_price,\n median(current_price)::int as median_price,\n avg(mileage_km)::int as avg_mileage,\n avg(year)::numeric(5,1) as avg_year\nfrom fl\ngroup by seller_internal_id\norder by stock desc))","inputQueryString":"select\n count(*) filter (where stock >= 5) as pro_sellers,\n count(*) filter (where stock < 5) as private_sellers,\n sum(stock) filter (where stock >= 5) as pro_listings,\n sum(stock) filter (where stock < 5) as private_listings,\n round(\n sum(stock) filter (where stock >= 5) * 1.0\n / nullif(sum(stock), 0), 3\n ) as pro_share_pct\nfrom (${seller_base_all})","compiled":true,"inline":true},{"id":"pro_private_profile","compiledQueryString":"select\n case when stock >= 5 then 'Professional' else 'Private' end as seller_category,\n count(*) as sellers,\n sum(stock) as listings,\n avg(avg_price)::int as avg_price,\n avg(median_price)::int as median_price,\n avg(avg_mileage)::int as avg_mileage,\n avg(avg_year)::numeric(5,1) as avg_year\nfrom ((with fl as (\n select l.seller_internal_id::varchar as seller_internal_id,\n COALESCE(sn.seller_name, l.seller_name) as seller_name,\n l.current_price, l.mileage_km, l.year\n from as24ch.listings l\n left join as24ch.seller_names sn\n on l.seller_internal_id::varchar = sn.seller_internal_id\n where l.status = 'active'\n and ('${inputs.brand.value}' = '' or l.brand = '${inputs.brand.value}')\n and ('${inputs.model.value}' = '' or '${inputs.brand.value}' = '' or l.model = '${inputs.model.value}')\n and (l.year is null or l.year between COALESCE(NULLIF('${inputs.year_min}', ''), '2016')::int and COALESCE(NULLIF('${inputs.year_max}', ''), '2026')::int)\n and (l.mileage_km is null or l.mileage_km between COALESCE(NULLIF('${inputs.min_km}', ''), '0')::int and COALESCE(NULLIF('${inputs.max_km}', ''), '300000')::int)\n)\nselect\n seller_internal_id,\n max(seller_name) as seller_name,\n count(*) as stock,\n avg(current_price)::int as avg_price,\n median(current_price)::int as median_price,\n avg(mileage_km)::int as avg_mileage,\n avg(year)::numeric(5,1) as avg_year\nfrom fl\ngroup by seller_internal_id\norder by stock desc))\ngroup by 1\norder by listings desc","inputQueryString":"select\n case when stock >= 5 then 'Professional' else 'Private' end as seller_category,\n count(*) as sellers,\n sum(stock) as listings,\n avg(avg_price)::int as avg_price,\n avg(median_price)::int as median_price,\n avg(avg_mileage)::int as avg_mileage,\n avg(avg_year)::numeric(5,1) as avg_year\nfrom (${seller_base_all})\ngroup by 1\norder by listings desc","compiled":true,"inline":true},{"id":"stock_dist","compiledQueryString":"select bucket, sort_order, count(*) as dealers\nfrom (\n select\n case\n when stock = 1 then '1'\n when stock <= 5 then '2-5'\n when stock <= 20 then '6-20'\n when stock <= 50 then '21-50'\n when stock <= 100 then '51-100'\n when stock <= 200 then '101-200'\n when stock <= 500 then '201-500'\n else '500+'\n end as bucket,\n case\n when stock = 1 then 1\n when stock <= 5 then 2\n when stock <= 20 then 3\n when stock <= 50 then 4\n when stock <= 100 then 5\n when stock <= 200 then 6\n when stock <= 500 then 7\n else 8\n end as sort_order\n from ((with fl as (\n select l.seller_internal_id::varchar as seller_internal_id,\n COALESCE(sn.seller_name, l.seller_name) as seller_name,\n l.current_price, l.mileage_km, l.year\n from as24ch.listings l\n left join as24ch.seller_names sn\n on l.seller_internal_id::varchar = sn.seller_internal_id\n where l.status = 'active'\n and ('${inputs.brand.value}' = '' or l.brand = '${inputs.brand.value}')\n and ('${inputs.model.value}' = '' or '${inputs.brand.value}' = '' or l.model = '${inputs.model.value}')\n and (l.year is null or l.year between COALESCE(NULLIF('${inputs.year_min}', ''), '2016')::int and COALESCE(NULLIF('${inputs.year_max}', ''), '2026')::int)\n and (l.mileage_km is null or l.mileage_km between COALESCE(NULLIF('${inputs.min_km}', ''), '0')::int and COALESCE(NULLIF('${inputs.max_km}', ''), '300000')::int)\n)\nselect\n seller_internal_id,\n max(seller_name) as seller_name,\n count(*) as stock,\n avg(current_price)::int as avg_price,\n median(current_price)::int as median_price,\n avg(mileage_km)::int as avg_mileage,\n avg(year)::numeric(5,1) as avg_year\nfrom fl\ngroup by seller_internal_id\norder by stock desc))\n) t\ngroup by bucket, sort_order\norder by sort_order","inputQueryString":"select bucket, sort_order, count(*) as dealers\nfrom (\n select\n case\n when stock = 1 then '1'\n when stock <= 5 then '2-5'\n when stock <= 20 then '6-20'\n when stock <= 50 then '21-50'\n when stock <= 100 then '51-100'\n when stock <= 200 then '101-200'\n when stock <= 500 then '201-500'\n else '500+'\n end as bucket,\n case\n when stock = 1 then 1\n when stock <= 5 then 2\n when stock <= 20 then 3\n when stock <= 50 then 4\n when stock <= 100 then 5\n when stock <= 200 then 6\n when stock <= 500 then 7\n else 8\n end as sort_order\n from (${seller_base_all})\n) t\ngroup by bucket, sort_order\norder by sort_order","compiled":true,"inline":true},{"id":"top20","compiledQueryString":"select\n COALESCE(seller_name, seller_internal_id::varchar) as seller_label,\n stock, avg_price\nfrom ((with fl as (\n select l.seller_internal_id::varchar as seller_internal_id,\n COALESCE(sn.seller_name, l.seller_name) as seller_name,\n l.current_price, l.mileage_km, l.year\n from as24ch.listings l\n left join as24ch.seller_names sn\n on l.seller_internal_id::varchar = sn.seller_internal_id\n where l.status = 'active'\n and ('${inputs.brand.value}' = '' or l.brand = '${inputs.brand.value}')\n and ('${inputs.model.value}' = '' or '${inputs.brand.value}' = '' or l.model = '${inputs.model.value}')\n and (l.year is null or l.year between COALESCE(NULLIF('${inputs.year_min}', ''), '2016')::int and COALESCE(NULLIF('${inputs.year_max}', ''), '2026')::int)\n and (l.mileage_km is null or l.mileage_km between COALESCE(NULLIF('${inputs.min_km}', ''), '0')::int and COALESCE(NULLIF('${inputs.max_km}', ''), '300000')::int)\n)\nselect\n seller_internal_id,\n max(seller_name) as seller_name,\n count(*) as stock,\n avg(current_price)::int as avg_price,\n median(current_price)::int as median_price,\n avg(mileage_km)::int as avg_mileage,\n avg(year)::numeric(5,1) as avg_year\nfrom fl\ngroup by seller_internal_id\nhaving count(*) >= cast('${inputs.min_stock.value}' as integer)\norder by stock desc\nlimit 500))\norder by stock desc\nlimit 20","inputQueryString":"select\n COALESCE(seller_name, seller_internal_id::varchar) as seller_label,\n stock, avg_price\nfrom (${seller_base})\norder by stock desc\nlimit 20","compiled":true,"inline":true}]}

AS24 CH — Sellers

Seller Analysis

Pro vs private profile

Stock size distribution

Top 20 sellers by stock

Seller table