-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathexport_analysis.sql
More file actions
298 lines (293 loc) · 7.94 KB
/
Copy pathexport_analysis.sql
File metadata and controls
298 lines (293 loc) · 7.94 KB
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
select column_name
from information_schema.columns
where table_name='export';
select count(*) as total_records
from export;
select sum(trade_value) as total_trade_value
from export;
select sum(net_weight) as total_net_weight
from export;
select year,
sum(trade_value)as total_trade_value
from export
group by year
order by year;
select commodity,
sum(trade_value) as total_trade_value
from export
group by commodity
order by total_trade_value desc;
select year,
commodity,
sum(trade_value) as total_trade_value
from export
group by year, commodity
order by year, commodity;
select partner,
sum(trade_value) as total_trade_value
from export
where commodity='Rice'
group by partner
order by total_trade_value desc
limit 11;
select partner,
sum(trade_value) as total_trade_value
from export
where commodity='Soyabean'
group by partner
order by total_trade_value desc
limit 11;
select commodity,
round(sum(trade_value)/sum(net_weight),2) as value_per_kg
from export
group by commodity;
select partner,
round(sum(trade_value)/sum(net_weight),2) as value_per_kg
from export
where commodity='Soyabean'
group by partner
order by value_per_kg desc
limit 11;
select commodity,
partner,
sum(trade_value) as total_trade_value
from export
group by commodity, partner
order by commodity, total_trade_value desc;
select year,
commodity,
sum(trade_value) as total_trade_value
from export
group by year, commodity
order by year, commodity;
select year, commodity,
round(sum(trade_value),2) as total_trade_value
from export
group by year, commodity
order by total_trade_value desc;
select commodity,
sum(trade_value) as total_trade_value
from export
group by commodity
having sum(trade_value)>100000000;
select commodity,
sum(trade_value) as total_trade_value
from export
group by commodity
having sum(trade_value)>100000000
order by total_trade_value;
select commodity, year,
round(avg(trade_value),2) as avg_trade_value
from export
group by commodity,year;
select commodity, year,
round(avg(trade_value),2) as avg_trade_value
from export
where commodity='Rice'
group by commodity,year
order by avg_trade_value desc;
select commodity, year,
round(avg(trade_value),2) as avg_trade_value
from export
where commodity='Soyabean'
group by commodity,year
order by avg_trade_value desc;
select year,commodity,
sum(trade_value) as total_trade_value,
case
when sum(trade_value)>1000000000 then 'high export'
else'low export'
end as category
from export
group by commodity,year
order by year desc;
select year,commodity,
sum(trade_value) as total_trade_value,
case
when sum(trade_value)>1000000000 then 'high export'
else'low export'
end as category
from export
where commodity='Rice'
group by commodity,year
order by year desc;
select year,commodity,
sum(trade_value) as total_trade_value,
case
when sum(trade_value)>1000000000 then 'high export'
else'low export'
end as category
from export
where commodity='Soyabean'
group by commodity,year
order by year desc;
select year,commodity,
round(sum(trade_value)/sum(net_weight),2) as value_per_kg,
case
when round(sum(trade_value)/sum(net_weight),2)>0.5 then 'high demand'
else'less demand'
end as category
from export
where commodity='Soyabean'
group by commodity,year
order by value_per_kg desc;
select year,commodity,
round(sum(trade_value)/sum(net_weight),2) as value_per_kg,
case
when round(sum(trade_value)/sum(net_weight),2)>0.5 then 'high demand'
else'less demand'
end as category
from export
where commodity='Rice'
group by commodity,year
order by value_per_kg desc;
select commodity,
count(*) as total_records
from export
group by commodity;
select partner,
round(sum(trade_value)*100.0/(select sum(trade_value)from export),
2) as market_share_pct
from export
group by partner
order by market_share_pct desc;
select commodity,
count(*) as total_records
from export
group by commodity;
select partner,commodity,
round(sum(trade_value)*100.0/(select sum(trade_value)from export),
2) as market_share_pct
from export
where commodity='Rice'
group by partner,commodity
order by market_share_pct desc;
select partner,commodity,
round(sum(trade_value)*100.0/(select sum(trade_value)from export),
2) as market_share_pct
from export
where commodity='Soyabean'
group by partner,commodity
order by market_share_pct desc;
select *
from export
where trade_value>
(select avg(trade_value)
from export);
select year,partner,commodity,
sum(trade_value) as total_trade_value
from export
group by year,partner,commodity
order by year,partner,commodity,total_trade_value desc;
select year,partner,commodity,
sum(trade_value) as total_trade_value
from export
where commodity='Rice'
group by year,partner,commodity
order by year,total_trade_value desc;
select year,partner,commodity,
sum(trade_value) as total_trade_value
from export
where commodity='Soyabean'
group by year,partner,commodity
order by year,total_trade_value desc;
select year,commodity,partner,
round(sum(trade_value)*100.0/(select sum(trade_value)from export),
2
) as contribution_pct
from export
group by year,commodity,partner
order by contribution_pct desc;
select year,commodity,partner,
round(sum(trade_value)*100.0/(select sum(trade_value)from export),
2
) as contribution_pct
from export
where commodity='Rice'
group by year,commodity,partner
order by contribution_pct desc;
select year,commodity,partner,
round(sum(trade_value)*100.0/(select sum(trade_value)from export),
2
) as contribution_pct
from export
where commodity='Soyabean'
group by year,commodity,partner
order by contribution_pct desc;
select partner, commodity,
sum(trade_value) as total_trade_value,
rank() over(order by sum(trade_value)desc) as partner_rnk
from export
group by commodity, partner;
select partner, commodity,
sum(trade_value) as total_trade_value,
rank() over(order by sum(trade_value)desc) as partner_rnk
from export
where commodity='Rice'
group by commodity, partner;
select partner, commodity,
sum(trade_value) as total_trade_value,
rank() over(order by sum(trade_value)desc) as partner_rnk
from export
where commodity='Soyabean'
group by commodity, partner;
select partner, commodity,
sum(net_weight) as total_net_weight,
rank() over(order by sum(net_weight)desc) as partner_rnk
from export
where commodity='Soyabean'
group by commodity, partner;
select partner, commodity,
sum(net_weight) as total_net_weight,
rank() over(order by sum(net_weight)desc) as partner_rnk
from export
where commodity='Rice'
group by commodity, partner;
select partner, commodity,
round(sum(trade_value)/sum(net_weight),2) as value_per_kg,
rank() over(order by round (sum(trade_value)/sum(net_weight),2)desc)as partner_rnk
from export
where commodity='Rice'
group by commodity, partner;
select partner, commodity,
round(sum(trade_value)/sum(net_weight),2) as value_per_kg,
rank() over(order by round (sum(trade_value)/sum(net_weight),2)desc)as partner_rnk
from export
where commodity='Soyabean'
group by commodity, partner;
select year, commodity,
round(sum(trade_value)/sum(net_weight),2) as value_per_kg,
rank() over(order by round(sum(trade_value)/sum(net_weight),2) desc) as year_rank
from export
where commodity='Rice'
group by year, commodity
order by year_rank desc;
select year, commodity,
round(sum(trade_value)/sum(net_weight),2) as value_per_kg,
rank() over(order by round(sum(trade_value)/sum(net_weight),2) desc) as year_rank
from export
where commodity='Soyabean'
group by year, commodity
order by year_rank desc;
with yearly_trade as (select year,partner,commodity,
sum(trade_value) as total_trade_value
from export
group by year,partner,commodity)
select *
from yearly_trade
order by year desc;
with partner_analysis as (select partner,year,commodity,
round(sum(trade_value)/sum(net_weight),2) as value_per_kg
from export
group by partner,year,commodity)
select partner,year,commodity,
rank() over(order by value_per_kg desc) as partner_rank
from partner_analysis;
with partner_analysis as (select partner,year,commodity,
round(sum(trade_value)/sum(net_weight),2) as value_per_kg
from export
where commodity='Rice'
group by partner,year,commodity)
select partner,year,commodity,
rank() over(order by value_per_kg desc) as partner_rank
from partner_analysis;