Total Transaction Volume
Given 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;
I'm pretty sure Exponent's answer is wrong.
In the snippet below, they use "pl.name = 'Telephones' to attempt to filter down to the Telephone transactions, but they do this within a LEFT JOIN which means all product_lines rows are returned.
Below is my solution. Also, I didn't see anywhere that said the "amount" column was in cents instead of dollars, but I still divided by 100 to be consistent with Exponent's answer.
with usd_conversions as ( select t.id as transaction_id , product_id , pl.name , t.amount , t.currency_code , er.rate , case when t.currency_code = 'USD' then t.amount / 100 else (t.amount / 100) * er.rate end as usd_amount from transactions as t left join exchange_rate as er on t.currency_code = er.source_currency_code and er.target_currency_code = 'USD' join products as p on t.product_id = p.id join product_lines as pl on pl.id = p.product_line_id ) select name , round(sum(usd_amount),2) as total_amount_in_dollars from usd_conversions as usd group by 1 having name = 'Telephones';