File size: 4,845 Bytes
2a6941d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
-- 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;