Buyer Yearly Order Volume Report

Easy

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: buyer_id (same value as users.user_id) join_date 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 1

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 2

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 3

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.

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

Loading problem...

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

Buyer Yearly Order Volume Report

Easy

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: buyer_id (same value as users.user_id) join_date 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 1

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 2

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 3

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.

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