Skip to main content
Talentd

Count the Number of Experiments - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: Strava
Practice this problem

Problem Statement

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.

The 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.

Approach Overview

Problem Overview: The Experiments table stores experiment records with a platform and experiment_name. You need to count how many experiments exist for every platform–experiment pair. The tricky part: the result must include combinations that do not appear in the table, returning 0 for those counts.

Approach 1: Simple GROUP BY Count (O(n) time, O(k) space)

The most direct idea is grouping rows by platform and experiment_name and counting them using GROUP BY. This scans the table once and aggregates counts for existing combinations. The issue is that SQL aggregation only returns groups that actually appear in the data. Missing combinations like ('Android','Sports') will not show up. This approach works only if the problem does not require zero-count rows. Time complexity is O(n) for scanning the table and building aggregates, with O(k) space for distinct groups.

Approach 2: CROSS JOIN + LEFT JOIN Aggregation (O(n) time, O(1) extra space)

The correct solution generates every required platform–experiment pair first, then attaches counts from the real data. Create a derived table containing the three platforms (Android, IOS, Web) and another containing the three experiment types (Reading, Sports, Programming). Use a CROSS JOIN to produce all nine combinations. Next, aggregate the Experiments table with GROUP BY platform, experiment_name to compute counts.

Finally, connect the generated combinations with the aggregated counts using a LEFT JOIN. Any combination without a matching record produces NULL, which you convert to 0 using COALESCE(). This guarantees every platform–experiment pair appears exactly once. The table scan and aggregation cost O(n), while the generated combination set is constant size (9 rows), so the overall complexity remains O(n) time and O(1) additional space.

This pattern appears frequently in SQL interview problems: generate a reference grid of expected values, then attach real data with LEFT JOIN. It combines concepts from SQL, database querying, and joins. Once you recognize the requirement to include missing combinations, the cross-join grid becomes the natural solution.

Recommended for interviews: The CROSS JOIN + LEFT JOIN approach. Interviewers expect you to notice that aggregation alone misses zero-count combinations. Generating the full set of pairs first shows strong SQL reasoning and understanding of join semantics.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Simple GROUP BY CountO(n)O(k)When only existing platform–experiment pairs need to be counted
CROSS JOIN + LEFT JOIN AggregationO(n)O(1)When all combinations must appear, including those with zero experiments

Video Solution

LeetCode Medium 1990 "Number of Experiments" Twitter Interview SQL Question Detailed Explanation • Everyday Data Science • 1,545 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Count the Number of Experiments easy or hard?
The problem is rated Medium because the counting logic itself is simple but recognizing the need to include missing combinations requires SQL join knowledge. Many developers initially write a GROUP BY query and miss the rows with zero counts.
Count the Number of Experiments Python/Java solution
This problem is designed as a SQL query challenge rather than a typical algorithm problem. The intended solution uses MySQL with aggregation and joins. In application code like Python or Java, you would typically execute the SQL query directly against the database.
How to solve Count the Number of Experiments in O(n)?
Aggregate the Experiments table with GROUP BY platform and experiment_name to compute counts in one scan. Generate all required platform and experiment_name combinations using a CROSS JOIN. Then LEFT JOIN the aggregated counts and replace NULL values with 0 using COALESCE().
What is the best approach for Count the Number of Experiments?
The best approach generates all platform–experiment combinations using a CROSS JOIN and then attaches real counts using a LEFT JOIN on an aggregated subquery. This ensures missing combinations still appear with a count of 0. The query scans the Experiments table once, resulting in O(n) time complexity.
Is Count the Number of Experiments asked at Google/Amazon/Meta?
Database aggregation and join problems like this commonly appear in SQL interviews at large tech companies. Variations of counting combinations with missing rows are frequently asked in analytics and data engineering interviews at companies such as Amazon, Google, and Meta.
What data structure is used in Count the Number of Experiments?
The problem relies on relational database operations rather than traditional data structures. Key SQL concepts include GROUP BY aggregation, CROSS JOIN to generate combinations, and LEFT JOIN to attach counts while preserving missing rows.
What is the time complexity of Count the Number of Experiments?
The optimal SQL solution runs in O(n) time where n is the number of rows in the Experiments table. The table is scanned once to compute counts using GROUP BY. The CROSS JOIN only produces a constant number of combinations (3 Ă— 3), so it does not affect the asymptotic complexity.

Ready to solve this problem?

Practice Count the Number of Experiments with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary