Count the Number of Experiments

Count the Number of Experiments - Problem

Database Medium

Table: Experiments

Column Name	Type
experiment_id	int
platform	enum
experiment_name	enum

experiment_id is the column with unique values for this table.
platform is an enum (category) type of values ('Android', 'IOS', 'Web').
experiment_name is an enum (category) type of 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 a solution 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.

Table Schema

Experiments

Column Name	Type	Description
`experiment_id` PK	int	Unique identifier for each experiment
`platform`	enum	Platform where experiment was conducted: Android, IOS, Web
`experiment_name`	enum	Type of experiment: Reading, Sports, Programming

Primary Key: experiment_id

Note: Contains experiment data with platform and experiment type information

Input & Output

Example 1 — Basic Experiment Count

Input Table:

experiment_id	platform	experiment_name
1	Android	Reading
2	Android	Reading
3	Android	Reading
4	IOS	Programming
5	IOS	Programming
6	Web	Programming

Output:

platform	experiment_name	num_experiments
Android	Programming	0
Android	Reading	3
Android	Sports	0
IOS	Programming	2
IOS	Reading	0
IOS	Sports	0
Web	Programming	1
Web	Reading	0
Web	Sports	0

💡 Note:

The query creates all 9 possible combinations of platforms (Android, IOS, Web) and experiments (Programming, Reading, Sports). Then it counts actual occurrences: Android-Reading has 3 experiments, IOS-Programming has 2, Web-Programming has 1, and all other combinations have 0.

Example 2 — All Zero Counts

Input Table:

experiment_id	platform	experiment_name

Output:

platform	experiment_name	num_experiments
Android	Programming	0
Android	Reading	0
Android	Sports	0
IOS	Programming	0
IOS	Reading	0
IOS	Sports	0
Web	Programming	0
Web	Reading	0
Web	Sports	0

💡 Note:

When the Experiments table is empty, the CROSS JOIN still generates all 9 possible platform-experiment combinations, but the LEFT JOIN finds no matches, resulting in all counts being 0.

Constraints

platform is one of 'Android', 'IOS', 'Web'
experiment_name is one of 'Reading', 'Sports', 'Programming'
experiment_id is unique for each row

Visualization

Tap to expand

Asked in

A Amazon 15 M Microsoft 12

Use CROSS JOIN to generate all platform-experiment combinations, then LEFT JOIN with actual data to count occurrences and handle zero counts.

Table Schema

Experiments

Column Name	Type	Description
`experiment_id` PK	int	Unique identifier for each experiment
`platform`	enum	Platform where experiment was conducted: Android, IOS, Web
`experiment_name`	enum	Type of experiment: Reading, Sports, Programming

Primary Key: experiment_id

Note: Contains experiment data with platform and experiment type information

Common Approaches

✓ Cross Join with Aggregation

⏱️ Time: O(n + k) Space: O(k)

Create a Cartesian product of all platforms and experiments, then LEFT JOIN with the actual data to count occurrences. This ensures all combinations appear in the result, even with zero counts.

Cross Join with Aggregation — Algorithm Steps

Step 1: Create all possible platform-experiment combinations using CROSS JOIN
Step 2: LEFT JOIN with actual experiment data to count occurrences
Step 3: Use COALESCE to handle NULL counts as zero

Visualization

Tap to expand

Step-by-Step Walkthrough

Cross Join

Create all platform-experiment pairs

Left Join

Match with actual data

Count

Aggregate and handle zeros

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n + k)

n for scanning experiments, k for cross product (constant 9)

✓ Linear Growth

Space Complexity

O(k)

k combinations stored (3 platforms × 3 experiments = 9)

✓ Linear Space

23.0K Views

Medium Frequency

~12 min Avg. Time

485 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Cross Join with Aggregation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler