Customer Order Count Distribution by Country
Apple● EasyFULLTIMEPHONE SCREENProblem statement
For every country, count its customers in three mutually exclusive order-frequency categories:
zero_order_customers: customers who have never placed an order.one_order_customers: customers who have placed exactly one order.multiple_order_customers: customers who have placed at least two orders.
Include every country, even when it has no customers. Customers with no matching row in orders must remain in the result.
Result
Return country_id, country_name, zero_order_customers, one_order_customers, and multiple_order_customers. Order rows by country_name ascending.
Table schema
MySQLPostgreSQLPandas
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
countries
| Column | Type | Nullable | Description |
|---|---|---|---|
| country_idPK | Integer | No | — |
| country_name | Text | No | — |
customers
| Column | Type | Nullable | Description |
|---|---|---|---|
| customer_idPK | Integer | No | — |
| country_id | Integer | No | — |
Foreign key: country_id → countries(country_id)
orders
| Column | Type | Nullable | Description |
|---|---|---|---|
| order_idPK | Integer | No | — |
| customer_id | Integer | No | — |
| country_id | Integer | No | — |
| order_date | Date | No | — |
Foreign key: customer_id → customers(customer_id)
Foreign key: country_id → countries(country_id)
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| country_id | Integer | No | — |
| country_name | Text | No | — |
| zero_order_customers | Integer | No | — |
| one_order_customers | Integer | No | — |
| multiple_order_customers | Integer | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- Each customer belongs to exactly one country.
- Every order belongs to exactly one customer, and its
country_idmatches that customer's country. - Every
country_nameis unique. - A customer's category is based on the number of distinct order rows for that customer.
- Each input table contains at most
2,000rows.