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.

Description Solution Video 15:09

1990. Count the Number of Experiments

Medium49.1% acceptance1 companyPremium elsewhere, free here

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.

Asked at 1 company

Strava

Topics

Database

15:09

Still stuck?Read the written solution, or watch a walkthrough.

Open solution

LanguageLoading editor…

1class Solution:
  def solve(self):
      pass


Starter codePython 3Spaces: 4

Examples

Case 1

Input

{"headers":{"Experiments":["experiment_id","platform","experiment_name"]},"rows":{"Experiments":[[4,"IOS","Programming"],[13,"IOS","Sports"],[14,"Android","Reading"],[8,"Web","Reading"],[12,"Web","Reading"],[18,"Web","Programming"]]}}

Your code is saved on this device.Solution