Experiment Run Count Grid by Platform and Study

Medium

A product research organization runs controlled experiments across multiple client platforms and study tracks. Table: Experiments experiment_id: unique identifier for an experiment execution row platform: execution platform, one of "Android", "IOS", or "Web" experiment_name: study track, one of "Reading", "Sports", or "Programming" Business requirement: Analytics dashboards must always show a complete platform-study matrix with explicit zeroes for missing cells. Task: Return the number of experiment rows for every pair in: platforms = {Android, IOS, Web} experiment_name = {Reading, Sports, Programming} Output requirements: Return exactly three columns: platform experiment_name num_experiments Include all 9 platform-study combinations. If a combination has no rows in Experiments, return num_experiments = 0 for that row. Output order may be arbitrary. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Experiments:
| experiment_id | platform | experiment_name |
|---------------|----------|-----------------|
| 4             | IOS      | Programming     |
| 13            | IOS      | Sports          |
| 14            | Android  | Reading         |
| 8             | Web      | Reading         |
| 12            | Web      | Reading         |
| 18            | Web      | Programming     |

Output

[
  {"platform":"Android","experiment_name":"Reading","num_experiments":1},
  {"platform":"Android","experiment_name":"Sports","num_experiments":0},
  {"platform":"Android","experiment_name":"Programming","num_experiments":0},
  {"platform":"IOS","experiment_name":"Reading","num_experiments":0},
  {"platform":"IOS","experiment_name":"Sports","num_experiments":1},
  {"platform":"IOS","experiment_name":"Programming","num_experiments":1},
  {"platform":"Web","experiment_name":"Reading","num_experiments":2},
  {"platform":"Web","experiment_name":"Sports","num_experiments":0},
  {"platform":"Web","experiment_name":"Programming","num_experiments":1}
]

Explanation

The output is a full 3x3 grid. Missing platform-study combinations are explicitly represented with zero counts.

Example 2

Input

Experiments:
| experiment_id | platform | experiment_name |
|---------------|----------|-----------------|
| 1             | Android  | Reading         |
| 2             | Android  | Reading         |
| 3             | Android  | Reading         |
| 4             | Android  | Sports          |
| 5             | Android  | Sports          |
| 6             | Web      | Programming     |

Output

[
  {"platform":"Android","experiment_name":"Reading","num_experiments":3},
  {"platform":"Android","experiment_name":"Sports","num_experiments":2},
  {"platform":"Android","experiment_name":"Programming","num_experiments":0},
  {"platform":"IOS","experiment_name":"Reading","num_experiments":0},
  {"platform":"IOS","experiment_name":"Sports","num_experiments":0},
  {"platform":"IOS","experiment_name":"Programming","num_experiments":0},
  {"platform":"Web","experiment_name":"Reading","num_experiments":0},
  {"platform":"Web","experiment_name":"Sports","num_experiments":0},
  {"platform":"Web","experiment_name":"Programming","num_experiments":1}
]

Explanation

Heavy concentration in a few cells must not remove empty cells from the final grid.

Example 3

Input

Experiments:
| experiment_id | platform | experiment_name |
|---------------|----------|-----------------|
| (no rows)     |

Output

[
  {"platform":"Android","experiment_name":"Reading","num_experiments":0},
  {"platform":"Android","experiment_name":"Sports","num_experiments":0},
  {"platform":"Android","experiment_name":"Programming","num_experiments":0},
  {"platform":"IOS","experiment_name":"Reading","num_experiments":0},
  {"platform":"IOS","experiment_name":"Sports","num_experiments":0},
  {"platform":"IOS","experiment_name":"Programming","num_experiments":0},
  {"platform":"Web","experiment_name":"Reading","num_experiments":0},
  {"platform":"Web","experiment_name":"Sports","num_experiments":0},
  {"platform":"Web","experiment_name":"Programming","num_experiments":0}
]

Explanation

Even with no experiment rows, the matrix still contains all nine combinations with zero values.

Constraints

0 <= number of rows in Experiments <= 300000
Experiments.experiment_id is unique
0 <= experiment_id <= 1000000000
platform is one of: Android, IOS, Web
experiment_name is one of: Reading, Sports, Programming
Return exactly three columns: platform, experiment_name, num_experiments
Output must include all 9 platform-study combinations
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: platform, experiment_name, num_experiments

Preparing your editor. You can read the problem while it loads.

Experiment Run Count Grid by Platform and Study

Medium

A product research organization runs controlled experiments across multiple client platforms and study tracks. Table: Experiments experiment_id: unique identifier for an experiment execution row platform: execution platform, one of "Android", "IOS", or "Web" experiment_name: study track, one of "Reading", "Sports", or "Programming" Business requirement: Analytics dashboards must always show a complete platform-study matrix with explicit zeroes for missing cells. Task: Return the number of experiment rows for every pair in: platforms = {Android, IOS, Web} experiment_name = {Reading, Sports, Programming} Output requirements: Return exactly three columns: platform experiment_name num_experiments Include all 9 platform-study combinations. If a combination has no rows in Experiments, return num_experiments = 0 for that row. Output order may be arbitrary. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Experiments:
| experiment_id | platform | experiment_name |
|---------------|----------|-----------------|
| 4             | IOS      | Programming     |
| 13            | IOS      | Sports          |
| 14            | Android  | Reading         |
| 8             | Web      | Reading         |
| 12            | Web      | Reading         |
| 18            | Web      | Programming     |

Output

[
  {"platform":"Android","experiment_name":"Reading","num_experiments":1},
  {"platform":"Android","experiment_name":"Sports","num_experiments":0},
  {"platform":"Android","experiment_name":"Programming","num_experiments":0},
  {"platform":"IOS","experiment_name":"Reading","num_experiments":0},
  {"platform":"IOS","experiment_name":"Sports","num_experiments":1},
  {"platform":"IOS","experiment_name":"Programming","num_experiments":1},
  {"platform":"Web","experiment_name":"Reading","num_experiments":2},
  {"platform":"Web","experiment_name":"Sports","num_experiments":0},
  {"platform":"Web","experiment_name":"Programming","num_experiments":1}
]

Explanation

The output is a full 3x3 grid. Missing platform-study combinations are explicitly represented with zero counts.

Example 2

Input

Experiments:
| experiment_id | platform | experiment_name |
|---------------|----------|-----------------|
| 1             | Android  | Reading         |
| 2             | Android  | Reading         |
| 3             | Android  | Reading         |
| 4             | Android  | Sports          |
| 5             | Android  | Sports          |
| 6             | Web      | Programming     |

Output

[
  {"platform":"Android","experiment_name":"Reading","num_experiments":3},
  {"platform":"Android","experiment_name":"Sports","num_experiments":2},
  {"platform":"Android","experiment_name":"Programming","num_experiments":0},
  {"platform":"IOS","experiment_name":"Reading","num_experiments":0},
  {"platform":"IOS","experiment_name":"Sports","num_experiments":0},
  {"platform":"IOS","experiment_name":"Programming","num_experiments":0},
  {"platform":"Web","experiment_name":"Reading","num_experiments":0},
  {"platform":"Web","experiment_name":"Sports","num_experiments":0},
  {"platform":"Web","experiment_name":"Programming","num_experiments":1}
]

Explanation

Heavy concentration in a few cells must not remove empty cells from the final grid.

Example 3

Input

Experiments:
| experiment_id | platform | experiment_name |
|---------------|----------|-----------------|
| (no rows)     |

Output

[
  {"platform":"Android","experiment_name":"Reading","num_experiments":0},
  {"platform":"Android","experiment_name":"Sports","num_experiments":0},
  {"platform":"Android","experiment_name":"Programming","num_experiments":0},
  {"platform":"IOS","experiment_name":"Reading","num_experiments":0},
  {"platform":"IOS","experiment_name":"Sports","num_experiments":0},
  {"platform":"IOS","experiment_name":"Programming","num_experiments":0},
  {"platform":"Web","experiment_name":"Reading","num_experiments":0},
  {"platform":"Web","experiment_name":"Sports","num_experiments":0},
  {"platform":"Web","experiment_name":"Programming","num_experiments":0}
]

Explanation

Even with no experiment rows, the matrix still contains all nine combinations with zero values.

Constraints

0 <= number of rows in Experiments <= 300000
Experiments.experiment_id is unique
0 <= experiment_id <= 1000000000
platform is one of: Android, IOS, Web
experiment_name is one of: Reading, Sports, Programming
Return exactly three columns: platform, experiment_name, num_experiments
Output must include all 9 platform-study combinations
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: platform, experiment_name, num_experiments