LeetCode 1990 - Count the Number of Experiments

This problem gives us a database table named Experiments. Each row represents a single experiment performed by a participant. Every experiment belongs to one platform and one experiment category.

leetcodemediumdatabase

LeetCode Problem 1990

Difficulty: 🟡 Medium
Topics: Database

Solution

Problem Understanding

This problem gives us a database table named Experiments. Each row represents a single experiment performed by a participant. Every experiment belongs to one platform and one experiment category.

The table contains three columns:

Column Meaning
experiment_id Unique identifier for the experiment
platform One of Android, IOS, or Web
experiment_name One of Reading, Sports, or Programming

The task is to generate a report showing how many experiments exist for every possible combination of platform and experiment type.

There are exactly three platforms and exactly three experiment categories, so there are always:

$$3 \times 3 = 9$$

possible (platform, experiment_name) pairs.

A very important detail is that combinations with zero experiments must still appear in the output. This requirement is what makes the problem slightly more interesting than a simple GROUP BY query.

For example, if there are no Sports experiments on Android, the output must still contain:

platform experiment_name num_experiments
Android Sports 0

The input represents raw experiment records, while the output represents aggregated counts for every possible category combination.

The constraints are small because the number of platforms and experiment categories is fixed. The main challenge is not performance, but ensuring that missing combinations are included correctly.

Several edge cases are important:

  • The table may contain no rows at all. In that case, we still need all 9 combinations with count 0.
  • Some platforms may never appear in the table.
  • Some experiment categories may never appear in the table.
  • A naive GROUP BY query only returns combinations that exist, which would incorrectly omit zero-count pairs.

Approaches

Brute Force Approach

The brute force idea is to manually check every possible (platform, experiment_name) pair against the entire table.

We first enumerate all 9 combinations:

  • Android + Reading
  • Android + Sports
  • Android + Programming
  • IOS + Reading
  • and so on.

For each combination, we scan the entire Experiments table and count how many rows match both the platform and experiment name.

This approach is correct because every row is examined for every possible pair, guaranteeing accurate counts.

However, it is inefficient because we repeatedly scan the table. If there are N experiment rows and 9 combinations, the complexity becomes O(9N). While 9 is constant here, this pattern does not scale well in more general aggregation problems.

Optimal Approach

The better solution is to aggregate the counts once using GROUP BY, then combine those results with all possible platform-experiment pairs.

The key observation is that SQL aggregation only returns combinations that actually exist. Therefore, we must explicitly generate all possible combinations ourselves.

The standard technique is:

  1. Create a small virtual table containing all platforms.
  2. Create another virtual table containing all experiment names.
  3. Use CROSS JOIN to generate every possible pair.
  4. Use a LEFT JOIN with aggregated counts from the Experiments table.
  5. Replace missing values with 0 using COALESCE.

This ensures that:

  • existing combinations receive their actual counts,
  • missing combinations still appear with zero.

Approach Comparison

Approach Time Complexity Space Complexity Notes
Brute Force O(9N) O(1) Repeatedly scans the table for every combination
Optimal O(N) O(1) Aggregates once, then joins with all combinations

Algorithm Walkthrough

Step 1: Create the platform list

We first create a temporary table containing:

  • Android
  • IOS
  • Web

This guarantees that every platform appears in the final output.

Step 2: Create the experiment list

Next, we create another temporary table containing:

  • Reading
  • Sports
  • Programming

This guarantees that every experiment category appears.

Step 3: Generate all possible pairs

We use a CROSS JOIN between the platform table and the experiment table.

A cross join creates every possible combination:

platform experiment_name
Android Reading
Android Sports
Android Programming
IOS Reading
... ...

This produces exactly 9 rows.

Step 4: Aggregate experiment counts

We now process the original Experiments table using:

GROUP BY platform, experiment_name

This gives us the actual counts that exist in the database.

For the sample input:

platform experiment_name count
IOS Programming 1
IOS Sports 1
Android Reading 1
Web Reading 2
Web Programming 1

Notice that missing combinations do not appear.

Step 5: Left join the counts

We perform a LEFT JOIN between the 9 generated combinations and the aggregated counts.

A left join keeps all rows from the left side, even if no matching count exists.

Step 6: Replace NULL with 0

Missing combinations produce NULL counts after the join.

We use:

COALESCE(count, 0)

to convert those NULL values into zero.

Why it works

The algorithm works because the cross join explicitly constructs the complete universe of valid (platform, experiment_name) pairs. The left join then attaches real counts wherever matching experiments exist. Since left joins preserve unmatched rows, every required combination appears exactly once. Finally, COALESCE converts missing counts into zero, satisfying the problem requirement.

Python Solution

# There is no Python solution for this database problem.
# The correct submission is an SQL query.

This problem belongs to the Database category on LeetCode, so the expected solution is written in SQL rather than Python. Unlike algorithmic problems, there is no class or function signature to implement.

The logic relies entirely on relational database operations:

  • generating combinations with CROSS JOIN,
  • counting rows with GROUP BY,
  • preserving missing combinations with LEFT JOIN,
  • converting missing values with COALESCE.

Go Solution

// There is no Go solution for this database problem.
// The correct submission is an SQL query.

Like the Python section, this problem is specifically a SQL database problem. LeetCode expects a standalone SQL query instead of executable Go code.

SQL Solution

WITH platforms AS (
    SELECT 'Android' AS platform
    UNION ALL
    SELECT 'IOS'
    UNION ALL
    SELECT 'Web'
),
experiments AS (
    SELECT 'Reading' AS experiment_name
    UNION ALL
    SELECT 'Sports'
    UNION ALL
    SELECT 'Programming'
),
experiment_counts AS (
    SELECT
        platform,
        experiment_name,
        COUNT(*) AS num_experiments
    FROM Experiments
    GROUP BY platform, experiment_name
)

SELECT
    p.platform,
    e.experiment_name,
    COALESCE(ec.num_experiments, 0) AS num_experiments
FROM platforms p
CROSS JOIN experiments e
LEFT JOIN experiment_counts ec
    ON p.platform = ec.platform
   AND e.experiment_name = ec.experiment_name;

The query is divided into three common table expressions for clarity.

The platforms CTE defines the three valid platforms.

The experiments CTE defines the three valid experiment categories.

The experiment_counts CTE computes the actual counts from the input table using GROUP BY.

After that:

  • CROSS JOIN generates all 9 combinations,
  • LEFT JOIN attaches matching counts,
  • COALESCE converts missing values into 0.

This guarantees that every required pair appears in the final result.

Worked Examples

Example 1

Input table:

experiment_id platform experiment_name
4 IOS Programming
13 IOS Sports
14 Android Reading
8 Web Reading
12 Web Reading
18 Web Programming

Step 1: Generate all combinations

The cross join produces:

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

Step 2: Aggregate counts

Using GROUP BY:

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

Step 3: Left join results

After joining:

platform experiment_name joined_count
Android Reading 1
Android Sports NULL
Android Programming NULL
IOS Reading NULL
IOS Sports 1
IOS Programming 1
Web Reading 2
Web Sports NULL
Web Programming 1

Step 4: Apply COALESCE

Replacing NULL with 0:

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

Complexity Analysis

Measure Complexity Explanation
Time O(N) The table is scanned once during aggregation
Space O(1) Only constant-sized temporary tables are created

The dominant operation is the GROUP BY aggregation over the Experiments table, which requires scanning all rows once.

The number of generated combinations is fixed at 9, so the cross join and left join operations are constant time relative to input size.

Test Cases

# Example case from the problem statement
# Validates normal counting behavior
assert True

# Empty table
# Should still produce all 9 combinations with count 0
assert True

# Only one platform appears
# Missing platforms must still exist in output
assert True

# Only one experiment category appears
# Missing categories must still exist
assert True

# All experiments belong to same pair
# One pair gets count > 0, others remain 0
assert True

# Multiple rows for same combination
# Counts must aggregate correctly
assert True

# Every combination appears exactly once
# No missing or duplicated rows
assert True

Since this is a database problem, traditional executable assert-based tests are not directly applicable in the same way as algorithmic coding problems. Testing is typically performed by executing the SQL query against sample tables.

Test Summary

Test Why
Sample input Verifies standard functionality
Empty table Ensures zero-count rows are preserved
Single platform only Confirms missing platforms still appear
Single experiment type only Confirms missing categories still appear
Repeated identical pairs Verifies aggregation correctness
All combinations present Ensures no rows are lost
Sparse dataset Validates LEFT JOIN behavior

Edge Cases

Empty Experiments Table

A very common mistake is assuming the table always contains rows. If the table is empty, a normal GROUP BY query returns no rows at all.

Our implementation handles this correctly because the cross join independently generates all 9 combinations first. Since the left join finds no matches, every count becomes NULL, and COALESCE converts them to 0.

Missing Platform Categories

Suppose the table only contains Web experiments.

A naive aggregation would only produce rows for Web, completely omitting Android and IOS.

Our solution avoids this issue because the platform list is manually defined and always included in the cross join.

Missing Experiment Categories

Suppose no Sports experiments exist.

A simple GROUP BY query would never produce a Sports row.

The explicit experiment category table ensures that every experiment type appears in the output, even when its count is zero.

Duplicate Experiment Rows

Multiple rows may belong to the same (platform, experiment_name) pair.

The COUNT(*) aggregation correctly counts all matching rows, ensuring accurate totals regardless of duplication frequency.