SET memory_limit='2500MB';
SET threads=2;
SET preserve_insertion_order=false;

CREATE OR REPLACE TEMP TABLE covered AS
SELECT DISTINCT store_id FROM read_json_auto('/root/ultron/data/mahally/details.jsonl',
  format='newline_delimited', ignore_errors=true,
  columns={id:'BIGINT', store_id:'BIGINT', dead:'BOOLEAN'})
WHERE dead IS NULL;

CREATE OR REPLACE TEMP TABLE meta AS
SELECT id, rating_count FROM read_json_auto('/root/ultron/data/mahally/meta.jsonl',
  format='newline_delimited', ignore_errors=true);

CREATE OR REPLACE TEMP TABLE pcount AS
SELECT store_id, count(*) AS prods
FROM read_json_auto('/root/ultron/data/mahally/products.jsonl', format='newline_delimited', ignore_errors=true,
  columns={id:'BIGINT', store_id:'BIGINT'})
GROUP BY store_id;

CREATE OR REPLACE TEMP TABLE rest AS
SELECT m.id, coalesce(m.rating_count,0) AS rc, coalesce(p.prods,0) AS prods
FROM meta m LEFT JOIN covered c ON c.store_id = m.id LEFT JOIN pcount p ON p.store_id = m.id
WHERE c.store_id IS NULL;

.print '=== COST OF THE REMAINDER — 3.5 KB per product fetch ==='
SELECT 'A. everything left'          AS option, count(*) AS stores, sum(prods) AS products,
       round(sum(prods) * 3.5 / 1048576.0, 2) AS gb FROM rest
UNION ALL
SELECT 'B. skip never-sold stores',  count(*), sum(prods), round(sum(prods)*3.5/1048576.0,2) FROM rest WHERE rc > 0
UNION ALL
SELECT 'C. stores w/ >=10 reviews',  count(*), sum(prods), round(sum(prods)*3.5/1048576.0,2) FROM rest WHERE rc >= 10
UNION ALL
SELECT 'D. stores w/ >=50 reviews',  count(*), sum(prods), round(sum(prods)*3.5/1048576.0,2) FROM rest WHERE rc >= 50
ORDER BY option;

.print ''
.print '=== where the unscraped TRACTION actually sits ==='
SELECT CASE WHEN rc = 0 THEN 'never sold'
            WHEN rc < 10 THEN '1-9 reviews'
            WHEN rc < 50 THEN '10-49'
            WHEN rc < 200 THEN '50-199'
            ELSE '200+' END AS band,
       count(*) AS stores, sum(prods) AS products, sum(rc) AS reviews,
       round(sum(rc)*100.0/(SELECT sum(rc) FROM rest), 1) AS pct_of_unscraped_traction
FROM rest GROUP BY 1 ORDER BY reviews DESC;
