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

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 |

[ {"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} ]

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 |

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 |