Count the Number of Experiments - Problem

You're working as a data analyst at a tech company that runs user experiments across different platforms. Your task is to create a comprehensive report showing the distribution of experiments.

Given a table Experiments with the following structure:

Column NameType
experiment_idint
platformenum ('Android', 'IOS', 'Web')
experiment_nameenum ('Reading', 'Sports', 'Programming')

Your goal: Generate a complete matrix showing the count of experiments for every possible combination of platform and experiment type.

Important: The result must include all 9 possible combinations (3 platforms ร— 3 experiments), even if some combinations have zero experiments. This ensures your report is complete and doesn't miss any gaps in testing coverage.

Return the results in any order, with columns: platform, experiment_name, num_experiments.

Input & Output

example_1.sql โ€” Standard Case
$ Input: Experiments table: +---------------+----------+-----------------+ | experiment_id | platform | experiment_name | +---------------+----------+-----------------+ | 1 | Android | Reading | | 2 | Android | Reading | | 3 | Android | Sports | | 4 | IOS | Programming | | 5 | IOS | Sports | | 6 | Web | Reading | +---------------+----------+-----------------+
โ€บ Output: +----------+-----------------+-----------------+ | platform | experiment_name | num_experiments | +----------+-----------------+-----------------+ | Android | Reading | 2 | | Android | Sports | 1 | | Android | Programming | 0 | | IOS | Reading | 0 | | IOS | Sports | 1 | | IOS | Programming | 1 | | Web | Reading | 1 | | Web | Sports | 0 | | Web | Programming | 0 | +----------+-----------------+-----------------+
๐Ÿ’ก Note: Shows all 9 possible combinations. Android-Reading appears twice (count=2), while missing combinations like Android-Programming show count=0.
example_2.sql โ€” Empty Table
$ Input: Experiments table: +---------------+----------+-----------------+ | experiment_id | platform | experiment_name | +---------------+----------+-----------------+ (empty)
โ€บ Output: +----------+-----------------+-----------------+ | platform | experiment_name | num_experiments | +----------+-----------------+-----------------+ | Android | Reading | 0 | | Android | Sports | 0 | | Android | Programming | 0 | | IOS | Reading | 0 | | IOS | Sports | 0 | | IOS | Programming | 0 | | Web | Reading | 0 | | Web | Sports | 0 | | Web | Programming | 0 | +----------+-----------------+-----------------+
๐Ÿ’ก Note: Even with no data, all 9 combinations must appear with zero counts. This ensures complete reporting coverage.
example_3.sql โ€” Single Platform Focus
$ Input: Experiments table: +---------------+----------+-----------------+ | experiment_id | platform | experiment_name | +---------------+----------+-----------------+ | 1 | Web | Reading | | 2 | Web | Reading | | 3 | Web | Programming | +---------------+----------+-----------------+
โ€บ Output: +----------+-----------------+-----------------+ | platform | experiment_name | num_experiments | +----------+-----------------+-----------------+ | Android | Reading | 0 | | Android | Sports | 0 | | Android | Programming | 0 | | IOS | Reading | 0 | | IOS | Sports | 0 | | IOS | Programming | 0 | | Web | Reading | 2 | | Web | Sports | 0 | | Web | Programming | 1 | +----------+-----------------+-----------------+
๐Ÿ’ก Note: Only Web platform has experiments, but Android and IOS combinations still appear with zero counts for complete analysis.

Visualization

Tap to expand
Complete Experiment Coverage AnalysisPlatform ร— Experiment MatrixPlatformReadingSportsProgrammingAndroid200IOS011Web100โœ“ Complete Coverage AchievedAll 9 combinations reported (including zeros)No gaps in experiment coverage analysis
Understanding the Visualization
1
Generate Matrix
Create all 9 combinations using CROSS JOIN of platforms and experiments
2
Match Data
LEFT JOIN with actual experiment records to find matching counts
3
Fill Gaps
Use COALESCE to ensure missing combinations show as 0 instead of NULL
Key Takeaway
๐ŸŽฏ Key Insight: CROSS JOIN ensures complete matrix coverage - every possible combination is represented, making it impossible to miss zero-count scenarios in your analysis.

Time & Space Complexity

Time Complexity
โฑ๏ธ
O(n)

Single table scan with efficient grouping and joining

n
2n
โœ“ Linear Growth
Space Complexity
O(1)

Only constant extra space for the 9 combinations matrix

n
2n
โœ“ Linear Space

Constraints

  • 1 โ‰ค experiment_id โ‰ค 106
  • platform is one of ('Android', 'IOS', 'Web')
  • experiment_name is one of ('Reading', 'Sports', 'Programming')
  • 0 โ‰ค number of rows โ‰ค 104
  • Result must include all 9 possible combinations
Asked in
Meta 45 Google 38 Amazon 32 Microsoft 28
23.4K Views
High Frequency
~15 min Avg. Time
892 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