Count the Number of Experiments

Count the Number of Experiments

What is this problem about?

The Count the Number of Experiments coding problem is a SQL-based challenge. You are given a table Experiments containing columns: platform (Android, iOS, Web), experiment_name (Reading, Sports, Programming), and experiment_id.

The goal is to generate a report that shows the count of experiments for every possible combination of platform and experiment name. If a specific combination has no experiments recorded, you must still include it in the output with a count of 0.

Why is this asked in interviews?

Companies like Strava use this to test proficiency with the database interview pattern involving "outer joins" or "cartesian products." It evaluates whether a candidate knows how to handle missing data. Most basic GROUP BY queries omit rows with zero counts; this problem requires the use of a reference table (usually created via a CROSS JOIN) to ensure all categories are represented.

Algorithmic pattern used

The SQL pattern used here is Cross Join followed by an Outer Join.

1. Create a "master" list of all possible platforms.
2. Create a "master" list of all possible experiment names.
3. Perform a CROSS JOIN between these two lists to get every possible pair.
4. Perform a LEFT JOIN from this master pair list to the actual Experiments table.
5. GROUP BY the platform and experiment name and COUNT(experiment_id).

Example explanation

Platforms: ['Web', 'Android'] Names: ['Reading', 'Sports']

1. Cross Join produces: (Web, Reading), (Web, Sports), (Android, Reading), (Android, Sports).
2. Actual Data: (Web, Reading, ID:1), (Web, Reading, ID:2), (Android, Sports, ID:3).
3. Left Join and Count:
   • (Web, Reading) -> 2
   • (Web, Sports) -> 0
   • (Android, Reading) -> 0
   • (Android, Sports) -> 1

Common mistakes candidates make

• Using a simple Group By: This results in missing rows for combinations with zero entries.
• Incorrect Join Type: Using an INNER JOIN, which also filters out zero-count rows.
• Counting the wrong column: Using COUNT(*) on the left-joined result might return 1 instead of 0 if not careful (since the master row exists). It's safer to count the primary key of the experiments table (COUNT(experiment_id)).

Interview preparation tip

Whenever a SQL question says "include all categories, even those with no data," immediately think of a Cross Join to create the full category space, followed by a Left Join. This is a standard template for professional report generation.