-- Headline questions over the ledger. Run with: -- sqlite3 -markdown data/eacl.db < scripts/findings.sql -- "Coalition" = manufacturer registered in a state with export restrictions -- against Russia (ref/countries.csv). Counts are documents, not shipments. .print '## 1. Documents naming a coalition manufacturer, Russian applicants, by year' SELECT substr(d.start_date,1,4) AS year, COUNT(*) AS docs, SUM(d.chp_tier IS NOT NULL) AS chpl_docs, COUNT(DISTINCT d.applicant_key) AS applicants FROM docs d JOIN applicants a ON a.applicant_key = d.applicant_key WHERE d.coalition_mfr = 1 AND a.country = 'RU' AND d.start_date BETWEEN '2018-01-01' AND '2026-12-31' GROUP BY 1 ORDER BY 1; .print '' .print '## 2. Who issues them: issuing country share, Russian applicant + coalition manufacturer' SELECT substr(d.start_date,1,4) AS year, COUNT(*) AS docs, ROUND(100.0*SUM(d.issuing_country='RU')/COUNT(*),1) AS pct_RU, ROUND(100.0*SUM(d.issuing_country='KG')/COUNT(*),1) AS pct_KG, ROUND(100.0*SUM(d.issuing_country='AM')/COUNT(*),1) AS pct_AM, ROUND(100.0*SUM(d.issuing_country='KZ')/COUNT(*),1) AS pct_KZ, ROUND(100.0*SUM(d.issuing_country='BY')/COUNT(*),1) AS pct_BY FROM docs d JOIN applicants a ON a.applicant_key = d.applicant_key WHERE d.coalition_mfr = 1 AND a.country = 'RU' AND d.start_date BETWEEN '2018-01-01' AND '2026-12-31' GROUP BY 1 ORDER BY 1; .print '' .print '## 3. Battlefield-goods (CHPL) documents with coalition manufacturers since 24 Feb 2022, by tier' SELECT d.chp_tier AS tier, COUNT(*) AS docs, COUNT(DISTINCT d.applicant_key) AS applicants, SUM(a.country='RU') AS ru_applicant_docs FROM docs d JOIN applicants a ON a.applicant_key = d.applicant_key WHERE d.chp_tier IS NOT NULL AND d.coalition_mfr = 1 AND d.start_date >= '2022-02-24' GROUP BY 1 ORDER BY 1; .print '' .print '## 4. Brands on documents since 24 Feb 2022 (Russian applicants)' SELECT b.brand, br.country, br.sector, COUNT(DISTINCT b.doc_key) AS docs, COUNT(DISTINCT d.applicant_key) AS ru_applicants, SUM(d.issuing_country <> 'RU') AS issued_outside_ru FROM doc_brands b JOIN docs d ON d.doc_key = b.doc_key JOIN applicants a ON a.applicant_key = d.applicant_key LEFT JOIN brands br ON br.brand = b.brand WHERE d.start_date >= '2022-02-24' AND a.country = 'RU' GROUP BY 1,2,3 ORDER BY docs DESC LIMIT 40; .print '' .print '## 5. Certification bodies outside Russia most used by Russian applicants for coalition goods since 2022' SELECT d.issuing_country AS cc, d.authority_id, substr(d.authority_name,1,70) AS authority, COUNT(*) AS docs, SUM(d.status_code IN ('02','03')) AS suspended_or_terminated FROM docs d JOIN applicants a ON a.applicant_key = d.applicant_key WHERE d.coalition_mfr = 1 AND a.country = 'RU' AND d.issuing_country <> 'RU' AND d.start_date >= '2022-02-24' GROUP BY 1,2,3 ORDER BY docs DESC LIMIT 20; .print '' .print '## 6. Short-lived documents: terminated within 30 days of issue, since 2022, by issuing country' SELECT d.issuing_country AS cc, COUNT(*) AS terminated_within_30d, SUM(d.coalition_mfr) AS with_coalition_mfr FROM docs d WHERE d.status_code = '03' AND d.start_date >= '2022-01-01' AND julianday(d.status_start) - julianday(d.start_date) BETWEEN 0 AND 30 GROUP BY 1 ORDER BY 2 DESC; .print '' .print '## 7. Largest groups of Russian applicants sharing one phone or e-mail (coalition-manufacturer docs since 2022)' WITH ru AS ( SELECT DISTINCT d.applicant_key FROM docs d JOIN applicants a ON a.applicant_key = d.applicant_key WHERE a.country = 'RU' AND d.coalition_mfr = 1 AND d.start_date >= '2022-02-24' ) SELECT c.channel, c.hash, COUNT(DISTINCT c.owner_key) AS applicants FROM contacts c JOIN ru ON ru.applicant_key = c.owner_key WHERE c.owner_kind = 'applicant' GROUP BY 1,2 HAVING applicants >= 5 ORDER BY applicants DESC LIMIT 15; .print '' .print '## 8. Applicants registered in occupied Ukrainian territory: documents by region and year' SELECT r.name AS region, substr(d.start_date,1,4) AS year, COUNT(*) AS docs, SUM(d.coalition_mfr) AS coalition_docs FROM docs d JOIN applicants a ON a.applicant_key = d.applicant_key JOIN regions r ON r.code = a.region AND r.ua_occupied = 1 WHERE d.start_date >= '2014-01-01' GROUP BY 1,2 ORDER BY 1,2; .print '' .print '## 9. Arms and ammunition (HS 9301-9306) with coalition manufacturers' SELECT substr(d.start_date,1,4) AS year, d.issuing_country AS cc, COUNT(DISTINCT d.doc_key) AS docs FROM docs d JOIN doc_hs h ON h.doc_key = d.doc_key WHERE substr(h.hs,1,4) BETWEEN '9301' AND '9306' AND d.coalition_mfr = 1 GROUP BY 1,2 ORDER BY 1,2; .print '' .print '## 10. Status changes and disappearances recorded by the ledger' SELECT event, COUNT(*) AS n, MIN(day) AS first, MAX(day) AS last FROM events WHERE event <> 'new' GROUP BY 1;