-
Notifications
You must be signed in to change notification settings - Fork 43
/
count-the-number-of-experiments.sql
148 lines (132 loc) · 4.37 KB
/
count-the-number-of-experiments.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
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
/*
Count the Number of Experiments Problem
Description
LeetCode Problem 1990.
Table: Experiments
+-----------------+------+
| Column Name | Type |
+-----------------+------+
| experiment_id | int |
| platform | enum |
| experiment_name | enum |
+-----------------+------+
experiment_id is the primary key for this table.
platform is an enum with one of the values ('Android', 'IOS', 'Web').
experiment_name is an enum with one of the values ('Reading', 'Sports', 'Programming').
This table contains information about the ID of an experiment done with a random person, the platform used to do the experiment, and the name of the experiment.
Write an SQL query to report the number of experiments done on each of the three platforms for each of the three given experiments. Notice that all the pairs of (platform, experiment) should be included in the output including the pairs with zero experiments.
Return the result table in any order.
The query result format is in the following example.
Example 1:
Input:
Experiments table:
+---------------+----------+-----------------+
| experiment_id | platform | experiment_name |
+---------------+----------+-----------------+
| 4 | IOS | Programming |
| 13 | IOS | Sports |
| 14 | Android | Reading |
| 8 | Web | Reading |
| 12 | Web | Reading |
| 18 | Web | Programming |
+---------------+----------+-----------------+
Output:
+----------+-----------------+-----------------+
| platform | experiment_name | num_experiments |
+----------+-----------------+-----------------+
| Android | Reading | 1 |
| Android | Sports | 0 |
| Android | Programming | 0 |
| IOS | Reading | 0 |
| IOS | Sports | 1 |
| IOS | Programming | 1 |
| Web | Reading | 2 |
| Web | Sports | 0 |
| Web | Programming | 1 |
+----------+-----------------+-----------------+
Explanation:
On the platform "Android", we had only one "Reading" experiment.
On the platform "IOS", we had one "Sports" experiment and one "Programming" experiment.
On the platform "Web", we had two "Reading" experiments and one "Programming" experiment.
*/
# V0
# IEEA : CROSS JOIN
WITH platforms_cte AS (
SELECT 'IOS' AS platform
UNION ALL
SELECT 'Android'
UNION ALL
SELECT 'Web'
),
experiment_names_cte AS (
SELECT 'Programming' AS experiment_name
UNION ALL
SELECT 'Sports'
UNION ALL
SELECT 'Reading'
),
platforms_and_experiments_cte AS (
SELECT * FROM platforms_cte CROSS JOIN experiment_names_cte
)
SELECT
a.platform,
a.experiment_name,
COUNT(b.platform) AS num_experiments
FROM
platforms_and_experiments_cte a
LEFT JOIN Experiments b
ON a.platform = b.platform AND a.experiment_name = b.experiment_name
GROUP BY a.platform, a.experiment_name
ORDER BY NULL;
# V1
# https://circlecoder.com/count-the-number-of-experiments/
select
platform,
experiment_name,
ifnull(num_experiments, 0) as num_experiments
from
(select "Android" as platform
union
select "IOS" as platform
union
select "Web" as platform) a
cross join (select "Reading" as experiment_name
union
select "Sports" as experiment_name
union
select "Programming" as experiment_name) b
left join (select platform, experiment_name, count(*) as num_experiments
from Experiments
group by 1, 2) c
using (platform, experiment_name)
order by 1, 2
# V2
# Time: O(n)
# Space: O(n)
WITH platforms_cte AS (
SELECT 'IOS' AS platform
UNION ALL
SELECT 'Android'
UNION ALL
SELECT 'Web'
),
experiment_names_cte AS (
SELECT 'Programming' AS experiment_name
UNION ALL
SELECT 'Sports'
UNION ALL
SELECT 'Reading'
),
platforms_and_experiments_cte AS (
SELECT * FROM platforms_cte CROSS JOIN experiment_names_cte
)
SELECT
a.platform,
a.experiment_name,
COUNT(b.platform) AS num_experiments
FROM
platforms_and_experiments_cte a
LEFT JOIN Experiments b
ON a.platform = b.platform AND a.experiment_name = b.experiment_name
GROUP BY a.platform, a.experiment_name
ORDER BY NULL;