Total Transaction Volume
HardGiven the e-commerce database schema below, write a SQL query to fetch the total transaction value in dollars (USD) for the product line "Telephones" and return it as total_amount_in_dollars.
You will need to use the exchange_rate table
Keep in mind that the amount field represents hundredths of the base currency.
Round up the result to two decimal points.
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:
total_amount_in_dollars ------------------------- float
The use of an INNER JOIN ensures that only transactions related to products within the "Telephones" product line are included, by directly linking transactions to products and then to their respective product lines, thus excluding unrelated transactions.
The Common Table Expression (CTE) simplifies the query by preprocessing data for currency conversion using CASE logic, which differentiates between transactions already in USD and those needing conversion based on their currency code, facilitating accurate aggregation of the total transaction value in USD.
Here's our full solution:
SQLWITH base AS (
SELECT
t.product_id,
CASE
WHEN t.currency_code = 'USD' THEN t.amount / 100.0
ELSE t.amount / 100.0 * er.rate
END AS amount_in_usd
FROM transactions t
LEFT JOIN exchange_rate er
ON t.currency_code = er.source_currency_code AND er.target_currency_code = 'USD'
INNER JOIN products p
ON t.product_id = p.id
INNER JOIN product_lines pl
ON p.product_line_id = pl.id AND pl.name = 'Telephones'
)
SELECT
ROUND(SUM(amount_in_usd), 2) AS total_amount_in_dollars -- Rounding up to the nearest cent
FROM base;