Download scripts/findings.sql from abr00kx/eac-ledger: direct link, hf CLI and curl.
- Browser
- Download file 4.85 kB
-
https://huggingface.co/abr00kx/eac-ledger/resolve/main/scripts/findings.sql
- Command line
-
hf download hf://abr00kx/eac-ledger/scripts/findings.sql
-
curl -L -o findings.sql https://huggingface.co/abr00kx/eac-ledger/resolve/main/scripts/findings.sql
4.85 kB
| -- 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; | |