00:00:00

Description

Editorial

Experiment Run Count Grid by Platform and Study

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Experiments =

[{"experiment_id":4,"platform":"IOS","experiment_name":"Programming"},{"experiment_id":13,"platform":"IOS","experiment_name":"Sports"},{"experiment_id":14,"platform":"Android","experiment_name":"Reading"},{"experiment_id":8,"platform":"Web","experiment_name":"Reading"},{"experiment_id":12,"platform":"Web","experiment_name":"Reading"},{"experiment_id":18,"platform":"Web","experiment_name":"Programming"}]

Loading problem...

101

00:00:00

Description

Editorial

Experiment Run Count Grid by Platform and Study

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Experiments =

Experiment Run Count Grid by Platform and Study

Hints

Experiment Run Count Grid by Platform and Study

Hints