Leetcode Problem 1990. Count the Number of Experiments

1990. Count the Number of Experiments

Leetcode Solutions

Cross Join with Conditional Aggregation

Create a derived table (or Common Table Expression - CTE) for all platforms.
Create a derived table (or CTE) for all experiment names.
Perform a CROSS JOIN between the platforms and experiment names to generate all possible combinations.
Perform a LEFT JOIN with the Experiments table on both platform and experiment_name.
Use GROUP BY to group the results by platform and experiment_name.
Use COUNT to count the number of experiments for each group, and COALESCE to convert NULL counts to zero.
Select the platform, experiment_name, and the count of experiments as num_experiments from the grouped result.


erDiagram
    Experiments {
        int experiment_id PK "Primary Key"
        enum platform "One of ('Android', 'IOS', 'Web')"
        enum experiment_name "One of ('Reading', 'Sports', 'Programming')"
    }

Dynamic Cross Join with Conditional Aggregation

Ask Question

Programming Language

Purpose:

General Question

Debug My Code

image/screenshot of info(optional)

Full Screen

Leetcode Problem 1990. Count the Number of Experiments

Leetcode Solutions

Cross Join with Conditional Aggregation

Solution Idea

Query Steps

Code Implementation

Dynamic Cross Join with Conditional Aggregation

Ask Question

Suggested Answer