Python3_workout/6-3.sql at master · Manix049/Python3_workout · 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
-- first join to filter just on vip_clients
select
    s.companyname,
    s.productcategory,
    count(distinct s.orderid) OrderCount,
    sum(s.saleamount) as TotalSales
from sales_all_years s
left semi join vip_clients v on (s.companyname = v.name)
group by
    s.companyname,
    s.productcategory

-- now add clients table for additional context
select
    s.companyname,
    s.productcategory,
    c.marketcaplabel,
    c.marketcapamount,
    c.name,
    c.ipoyear,
    c.symbol,
    count(distinct s.orderid) OrderCount,
    sum(s.saleamount) as TotalSales
from sales_all_years s
left semi join vip_clients v on (s.companyname = v.name)
join clients c on s.companyname = c.name
group by
    s.companyname,
    s.productcategory,
    c.marketcaplabel,
    c.marketcapamount,
    c.name,
    c.ipoyear,
    c.symbol