00:00:00

Description

Editorial

Buyer Yearly Order Volume Report

EASY10 pts

An online marketplace tracks account registrations, order transactions, and item metadata using three relational tables:

users columns:

user_id: unique user identifier
join_date: account creation date
favorite_brand: user profile preference

orders columns:

order_id: unique transaction identifier
order_date: transaction date
item_id: referenced item identifier
buyer_id: purchasing user
seller_id: selling user

items columns:

item_id: unique catalog identifier
item_brand: catalog brand label

Task:

For every user in users, return one output row containing:
1. buyer_id (same value as users.user_id)
2. join_date
3. orders_in_2019
orders_in_2019 is the number of orders where:
- orders.buyer_id = users.user_id
- order_date falls inside calendar year 2019
Users with zero qualifying orders must still appear with orders_in_2019 = 0.

Output requirements:

Return columns: buyer_id, join_date, orders_in_2019
Row order is not required unless your local runner enforces deterministic sorting

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

users:
| user_id | join_date  | favorite_brand |
|---------|------------|----------------|
| 1       | 2018-01-01 | Lenovo         |
| 2       | 2018-02-09 | Samsung        |
| 3       | 2018-01-19 | LG             |
| 4       | 2018-05-21 | HP             |

orders:
| order_id | order_date  | item_id | buyer_id | seller_id |
|----------|-------------|---------|----------|-----------|
| 1        | 2019-08-01  | 4       | 1        | 2         |
| 2        | 2018-08-02  | 2       | 1        | 3         |
| 3        | 2019-08-03  | 3       | 2        | 3         |
| 4        | 2018-08-04  | 1       | 4        | 2         |
| 5        | 2018-08-04  | 1       | 3        | 4         |
| 6        | 2019-08-05  | 2       | 2        | 4         |

items:
| item_id | item_brand |
|---------|------------|
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |

Output

[
  {"buyer_id":1,"join_date":"2018-01-01","orders_in_2019":1},
  {"buyer_id":2,"join_date":"2018-02-09","orders_in_2019":2},
  {"buyer_id":3,"join_date":"2018-01-19","orders_in_2019":0},
  {"buyer_id":4,"join_date":"2018-05-21","orders_in_2019":0}
]

Explanation

Each row is keyed by users.user_id. Only orders where the user appears as buyer and order_date is in 2019 are counted.

Example

Input

users:
| user_id | join_date  | favorite_brand |
|---------|------------|----------------|
| 10      | 2019-01-01 | Apex           |
| 11      | 2019-03-11 | Nova           |
| 12      | 2019-07-15 | Flux           |

orders:
| order_id | order_date  | item_id | buyer_id | seller_id |
|----------|-------------|---------|----------|-----------|
| 101      | 2019-01-01  | 1       | 10       | 11        |
| 102      | 2019-12-31  | 1       | 10       | 12        |
| 103      | 2020-01-01  | 2       | 10       | 11        |
| 104      | 2019-06-15  | 2       | 11       | 10        |

items:
| item_id | item_brand |
|---------|------------|
| 1       | Apex       |
| 2       | Nova       |

Output

[
  {"buyer_id":10,"join_date":"2019-01-01","orders_in_2019":2},
  {"buyer_id":11,"join_date":"2019-03-11","orders_in_2019":1},
  {"buyer_id":12,"join_date":"2019-07-15","orders_in_2019":0}
]

Explanation

Both 2019-01-01 and 2019-12-31 are inside the target year. 2020-01-01 is excluded.

Example

Input

users:
| user_id | join_date  | favorite_brand |
|---------|------------|----------------|
| 21      | 2017-09-10 | Arbor          |
| 22      | 2018-02-05 | Pixel          |

orders:
| order_id | order_date  | item_id | buyer_id | seller_id |
|----------|-------------|---------|----------|-----------|
| 900      | 2018-12-31  | 7       | 21       | 22        |
| 901      | 2020-01-01  | 8       | 22       | 21        |

items:
| item_id | item_brand |
|---------|------------|
| 7       | Arbor      |
| 8       | Pixel      |

Output

[
  {"buyer_id":21,"join_date":"2017-09-10","orders_in_2019":0},
  {"buyer_id":22,"join_date":"2018-02-05","orders_in_2019":0}
]

Explanation

Every user must appear even when no qualifying 2019 purchase exists.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in users <= 200000
0 <= number of rows in orders <= 400000
1 <= number of rows in items <= 200000
users.user_id is unique
orders.order_id is unique
items.item_id is unique
orders.item_id references items.item_id
orders.buyer_id and orders.seller_id reference users.user_id
join_date and order_date are valid YYYY-MM-DD dates
Count only orders where the user is the buyer
Include users with zero qualifying 2019 orders
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: buyer_id, join_date, orders_in_2019

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

items =

[{"item_id":1,"item_brand":"Samsung"},{"item_id":2,"item_brand":"Lenovo"},{"item_id":3,"item_brand":"LG"},{"item_id":4,"item_brand":"HP"}]

users =

[{"user_id":1,"join_date":"2018-01-01","favorite_brand":"Lenovo"},{"user_id":2,"join_date":"2018-02-09","favorite_brand":"Samsung"},{"user_id":3,"join_date":"2018-01-19","favorite_brand":"LG"},{"user_id":4,"join_date":"2018-05-21","favorite_brand":"HP"}]

orders =

[{"order_id":1,"order_date":"2019-08-01","item_id":4,"buyer_id":1,"seller_id":2},{"order_id":2,"order_date":"2018-08-02","item_id":2,"buyer_id":1,"seller_id":3},{"order_id":3,"order_date":"2019-08-03","item_id":3,"buyer_id":2,"seller_id":3},{"order_id":4,"order_date":"2018-08-04","item_id":1,"buyer_id":4,"seller_id":2},{"order_id":5,"order_date":"2018-08-04","item_id":1,"buyer_id":3,"seller_id":4},{"order_id":6,"order_date":"2019-08-05","item_id":2,"buyer_id":2,"seller_id":4}]

Loading problem...

101

00:00:00

Description

Editorial

Buyer Yearly Order Volume Report

EASY10 pts

An online marketplace tracks account registrations, order transactions, and item metadata using three relational tables:

users columns:

user_id: unique user identifier
join_date: account creation date
favorite_brand: user profile preference

orders columns:

order_id: unique transaction identifier
order_date: transaction date
item_id: referenced item identifier
buyer_id: purchasing user
seller_id: selling user

items columns:

item_id: unique catalog identifier
item_brand: catalog brand label

Task:

For every user in users, return one output row containing:
1. buyer_id (same value as users.user_id)
2. join_date
3. orders_in_2019
orders_in_2019 is the number of orders where:
- orders.buyer_id = users.user_id
- order_date falls inside calendar year 2019
Users with zero qualifying orders must still appear with orders_in_2019 = 0.

Output requirements:

Return columns: buyer_id, join_date, orders_in_2019
Row order is not required unless your local runner enforces deterministic sorting

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

users:
| user_id | join_date  | favorite_brand |
|---------|------------|----------------|
| 1       | 2018-01-01 | Lenovo         |
| 2       | 2018-02-09 | Samsung        |
| 3       | 2018-01-19 | LG             |
| 4       | 2018-05-21 | HP             |

orders:
| order_id | order_date  | item_id | buyer_id | seller_id |
|----------|-------------|---------|----------|-----------|
| 1        | 2019-08-01  | 4       | 1        | 2         |
| 2        | 2018-08-02  | 2       | 1        | 3         |
| 3        | 2019-08-03  | 3       | 2        | 3         |
| 4        | 2018-08-04  | 1       | 4        | 2         |
| 5        | 2018-08-04  | 1       | 3        | 4         |
| 6        | 2019-08-05  | 2       | 2        | 4         |

items:
| item_id | item_brand |
|---------|------------|
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |

Output

[
  {"buyer_id":1,"join_date":"2018-01-01","orders_in_2019":1},
  {"buyer_id":2,"join_date":"2018-02-09","orders_in_2019":2},
  {"buyer_id":3,"join_date":"2018-01-19","orders_in_2019":0},
  {"buyer_id":4,"join_date":"2018-05-21","orders_in_2019":0}
]

Explanation

Each row is keyed by users.user_id. Only orders where the user appears as buyer and order_date is in 2019 are counted.

Example

Input

users:
| user_id | join_date  | favorite_brand |
|---------|------------|----------------|
| 10      | 2019-01-01 | Apex           |
| 11      | 2019-03-11 | Nova           |
| 12      | 2019-07-15 | Flux           |

orders:
| order_id | order_date  | item_id | buyer_id | seller_id |
|----------|-------------|---------|----------|-----------|
| 101      | 2019-01-01  | 1       | 10       | 11        |
| 102      | 2019-12-31  | 1       | 10       | 12        |
| 103      | 2020-01-01  | 2       | 10       | 11        |
| 104      | 2019-06-15  | 2       | 11       | 10        |

items:
| item_id | item_brand |
|---------|------------|
| 1       | Apex       |
| 2       | Nova       |

Output

[
  {"buyer_id":10,"join_date":"2019-01-01","orders_in_2019":2},
  {"buyer_id":11,"join_date":"2019-03-11","orders_in_2019":1},
  {"buyer_id":12,"join_date":"2019-07-15","orders_in_2019":0}
]

Explanation

Both 2019-01-01 and 2019-12-31 are inside the target year. 2020-01-01 is excluded.

Example

Input

users:
| user_id | join_date  | favorite_brand |
|---------|------------|----------------|
| 21      | 2017-09-10 | Arbor          |
| 22      | 2018-02-05 | Pixel          |

orders:
| order_id | order_date  | item_id | buyer_id | seller_id |
|----------|-------------|---------|----------|-----------|
| 900      | 2018-12-31  | 7       | 21       | 22        |
| 901      | 2020-01-01  | 8       | 22       | 21        |

items:
| item_id | item_brand |
|---------|------------|
| 7       | Arbor      |
| 8       | Pixel      |

Output

[
  {"buyer_id":21,"join_date":"2017-09-10","orders_in_2019":0},
  {"buyer_id":22,"join_date":"2018-02-05","orders_in_2019":0}
]

Explanation

Every user must appear even when no qualifying 2019 purchase exists.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in users <= 200000
0 <= number of rows in orders <= 400000
1 <= number of rows in items <= 200000
users.user_id is unique
orders.order_id is unique
items.item_id is unique
orders.item_id references items.item_id
orders.buyer_id and orders.seller_id reference users.user_id
join_date and order_date are valid YYYY-MM-DD dates
Count only orders where the user is the buyer
Include users with zero qualifying 2019 orders
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: buyer_id, join_date, orders_in_2019

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

items =

[{"item_id":1,"item_brand":"Samsung"},{"item_id":2,"item_brand":"Lenovo"},{"item_id":3,"item_brand":"LG"},{"item_id":4,"item_brand":"HP"}]

users =

orders =

Buyer Yearly Order Volume Report

Hints

Buyer Yearly Order Volume Report

Hints