Sales by Customer City
MediumPremium
Write a query to fetch the number of transactions per user city, ordered by descending number of transactions, using an e-commerce database described by the schema shown below:
users products +---------------+---------+ +-----------------+---------+ +--| id | int | +-----| id | int | | | first_name | varchar | | | name | varchar | | | last_name | varchar | | +->| product_line_id | date | | | user_city | int | | | | stock | int | | | email | int | | | +-----------------+---------+ | +---------------+---------+ | | | | | | transactions | | product_lines | +---------------+---------+ | | +--------+--------+ +---->| id | int |<----+ +--| id | int | | customer_id | int | | name | varchar| | product_id | int | +--------+--------+ | amount | int | | currency_code | varchar | | date | date | +---------------+---------+ exchange_rate +----------------------+---------+ | id | int | | source_currency_code | varchar | | target_currency_code | varchar | | rate | numeric | +----------------------+---------+
Your answer should return a result with the following format:
user_city | number_of_transactions -----------+------------------------ varchar | int
This questions tests basic concepts such as the GROUP BY clause, aggregation functions, joins and the ORDER BY clause. Here's our complete solution to the question:
SELECT
u.user_city,
count(*) as number_of_transactions
FROM
users u
LEFT JOIN
transactions t
ON
u.id = t.customer_id
GROUP BY
u.user_city
ORDER BY
number_of_transactions DESC;This query will output a result with the following format:
user_city | number_of_transactions -----------+------------------------ Seattle | 30 Chicago | 16 New York | 4
The unique id is not clear in this question