-
Notifications
You must be signed in to change notification settings - Fork 3
/
OLAP_CUBE.sql
37 lines (32 loc) · 974 Bytes
/
OLAP_CUBE.sql
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
/*Note - If implemented in OLAP enabled environment, the below queries would use ROLLUP or CUBE
functions rather than unioned queries with different group by conditions.*/
SELECT
partner,
channel,
count(*) as Total,
SUM(converted) AS Converted,
SUM(converted)/COUNT(*) AS ConversionRate,
AVG(raw_score) as AvgRawScore
FROM lead_view
GROUP BY partner, channel
UNION
SELECT
partner,
"" AS channel,
count(*) as Total,
SUM(converted) AS Converted,
SUM(converted)/COUNT(*) AS ConversionRate,
AVG(raw_score) as AvgRawScore
FROM lead_view
GROUP BY partner
UNION
SELECT
"" as partner,
channel,
count(*) as Total,
SUM(converted) AS Converted,
SUM(converted)/COUNT(*) AS ConversionRate,
AVG(raw_score) as AvgRawScore
FROM lead_view
GROUP BY channel
;