Count the Number of Experiments - Solution & Explanation

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.