Count the Number of Experiments — LeetCode 1990 Python Solution

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').

+-----------------+------+
| 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.

import duckdb
import pandas as pd

def solution(experiments: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Experiments", experiments)
    return con.execute("""WITH
    P AS (
        SELECT 'Android' AS platform
        UNION
        SELECT 'IOS'
        UNION
        SELECT 'Web'
    ),
    Exp AS (
        SELECT 'Reading' AS experiment_name
        UNION
        SELECT 'Sports'
        UNION
        SELECT 'Programming'
    ),
    T AS (
        SELECT *
        FROM
            P,
            Exp
    )
SELECT platform, experiment_name, COUNT(experiment_id) AS num_experiments
FROM
    T AS t
    LEFT JOIN Experiments USING (platform, experiment_name)
GROUP BY 1, 2;""").df()