Count the Number of Experiments - Problem

Table: Experiments

Column NameType
experiment_idint
platformenum
experiment_nameenum
  • 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
Count the Number of Experiments - Complete MatrixInput: Sparse DataplatformexperimentAndroidReadingIOSProgrammingWebProgrammingCROSS JOINGenerate AllCombinationsOutput: Complete Matrix (9 rows)platformexperimentcountAndroidReading1AndroidSports0IOSProgramming1... (6 more rows)✓ All 9 combinations included3 platforms × 3 experiments = 9 total rows
Understanding the Visualization
1
Input
Sparse experiment data
2
Cross Join
Generate all combinations
3
Output
Complete matrix with counts
Key Takeaway
🎯 Key Insight: Use CROSS JOIN to ensure all possible combinations appear in the result, even with zero counts

Related Problems

Asked in
Amazon 15 Microsoft 12
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