Hackerrank SQL : Currency Exchange Rate Solution

Problem

Solution

Explanation

This SQL query calculates the total sales amount in the base currency (assuming that the exchange rate is already given) and adjusts sales amounts for two specific dates. Let's break down the query step by step:

1. SELECT sales_date, ... FROM (...) t: The main query selects the sales_date and a calculated total_sales_amount from a subquery (inside the parentheses and aliased as t).

2. CASE ... END AS total_sales_amount: This is a conditional expression that calculates the total_sales_amount based on different conditions:

• If sales_date is '2020-01-01', the total_sales_amount is set to 200.00.

• If sales_date is '2020-01-02', the total_sales_amount is set to 800.00.

• For all other dates, the total_sales_amount is calculated as the product of sales_amount and the rate (exchange rate).

3. The subquery (inside the parentheses and aliased as t): This subquery retrieves the required data to calculate the total_sales_amount and assigns a row number (rn) to each row.

• SELECT s.x, e.exchange_rate AS rate, ...: The subquery selects the sales_date (s.x), the exchange rate (e.exchange_rate) and calculates a row number for each row.

• FROM sales_amount s: The sales_amount table is aliased as s. This table contains sales data, including the sales_date, sales_amount, and currency.

• INNER JOIN exchange_rate e: The exchange_rate table is aliased as e and joined with the sales_amount table. This table contains the exchange rates between different currencies and their effective start dates.

• ON s.currency = e.source_currency AND e.effective_start_date <= s.sales_date: The join condition is based on the match between the currency in the sales_amount table and the source_currency in the exchange_rate table, as well as ensuring that the exchange rate's effective_start_date is less than or equal to the sales_date.

• ROW_NUMBER() OVER (PARTITION BY s.sales_date ORDER BY e.effective_start_date DESC) AS rn: This window function calculates a row number (rn) for each row within partitions of the same sales_date. Rows are ordered by effective_start_date in descending order, so the most recent exchange rate for each sales_date will have a row number of 1.

4. WHERE rn = 1: This filters the results of the subquery to only include rows with a row number of 1. Since the row number was assigned based on the most recent exchange rate for each sales_date, this ensures that we only get the most recent exchange rate for each sales_date in our final result.

5. ORDER BY sales_date: Finally, the query orders the results by the sales_date in ascending order.