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);

.print '=== SHARE OF ACTUAL MARKETPLACE TRACTION ALREADY CAPTURED ==='
SELECT
  CASE WHEN c.store_id IS NULL THEN 'not scraped' ELSE 'scraped' END AS grp,
  count(*) AS stores,
  sum(coalesce(m.rating_count, 0)) AS total_reviews,
  round(sum(coalesce(m.rating_count, 0)) * 100.0
        / (SELECT sum(coalesce(rating_count, 0)) FROM meta), 1) AS pct_of_traction
FROM meta m LEFT JOIN covered c ON c.store_id = m.id
GROUP BY 1 ORDER BY 1;

.print ''
.print '=== how many unscraped stores have ZERO reviews (never sold anything) ==='
SELECT count(*) AS unscraped_stores,
       count(*) FILTER (WHERE coalesce(m.rating_count,0) = 0) AS zero_review_stores,
       round(count(*) FILTER (WHERE coalesce(m.rating_count,0) = 0) * 100.0 / count(*), 1) AS pct_dead
FROM meta m LEFT JOIN covered c ON c.store_id = m.id WHERE c.store_id IS NULL;
