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.

Solution

Method 1 – Cross Join and Group By (SQL, Pandas)

Intuition

We need to report the count of experiments for every (platform, experiment_name) pair, including those with zero experiments. This is a classic case for a cross join (to generate all possible pairs) and a left join/group by to count the actual experiments.

Approach

Generate all possible pairs of (platform, experiment_name) using a cross join.
Left join this with the Experiments table to count the number of experiments for each pair.
Group by platform and experiment_name, and count the number of experiments (use 0 for missing pairs).

Code

[{"language":"MySQL","code":"SELECT p.platform, e.experiment_name, COUNT(experiment_id) AS num_experiments\nFROM (SELECT 'Android' AS platform UNION ALL SELECT 'IOS' UNION ALL SELECT 'Web') p\nCROSS JOIN (SELECT 'Reading' AS experiment_name UNION ALL SELECT 'Sports' UNION ALL SELECT 'Programming') e\nLEFT JOIN Experiments x\n  ON x.platform = p.platform AND x.experiment_name = e.experiment_name\nGROUP BY p.platform, e.experiment_name;","lang":"sql","html":"<pre class=\"shiki shiki-themes github-light github-dark\" style=\"background-color:#fff;--shiki-dark-bg:#24292e;color:#24292e;--shiki-dark:#e1e4e8\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">SELECT</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\"> p</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">.</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">platform</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">, </span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">e</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">.</span>&#

Related Tags