advanced-sql/formatter.sql at main · luiul/advanced-sql · GitHub

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
with -- seller CTEs
seller as (
    select
        *,
        sum(value) over(partition by country) as export
    from
        trades t
        join companies c on t.seller = c.name
),
seller_nodups as (
    select
        distinct country,
        export
    from
        seller
),
seller_full as (
    select
        distinct c.country,
        coalesce(b.export, 0) as export
    from
        seller_nodups b
        right outer join companies c on b.country = c.country
),
-- buyer CTEs (copy seller CTEs and adjust)
buyer as (
    select
        *,
        sum(value) over(partition by country) as i
    from
        trades t
        join companies c on t.buyer = c.name
),
buyer_nodups as (
    select
        distinct country,
        i
    from
        buyer
),
buyer_full as (
    select
        distinct c.country,
        coalesce(b.i, 0) as i
    from
        buyer_nodups b
        right outer join companies c on b.country = c.country
)
select
    b.country,
    export,
    i as "import"
from
    seller_full b
    join buyer_full s on b.country = s.country
order by
    b.country;

---

with tally as (
    select
        *,
        case
            when opinion = 'recommended' then 1
            else -1
        end as tally
    from
        opinions
),
cume_tally as (
    select
        *,
        sum(tally) over (partition by place) as cume_tally
    from
        tally
)
select
    distinct place
from
    cume_tally
where
    cume_tally > 0;