eac-ledger / scripts /findings.sql
runitx's picture
Claude Opus 5.5
eac-ledger: ledger of the EAEU register of conformity documents
2a6941d unverified
Raw History Blame Contribute Delete
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;