Write a SQL Query to find Total Sales Amount in USD for each sales date

 

 

 

 

CREATE TABLE SALES(

SALES_DATE DATE,

SALES_AMOUNT NUMBER,

CURRENCY VARCHAR(10)

);INSERT INTO SALES VALUES('2021-01-01','500','INR');

INSERT INTO SALES VALUES('2021-01-01','100','GBP');

INSERT INTO SALES VALUES('2021-01-02','1000','INR');

INSERT INTO SALES VALUES('2021-01-02','500','GBP');

INSERT INTO SALES VALUES('2021-01-03','500','INR');

INSERT INTO SALES VALUES('2021-01-17','200','GBP');CREATE TABLE EXCHANGE_RATES(

FROM_CURRENCY VARCHAR(10),

TO_CURRENCY VARCHAR(10),

EXCHANGE_RATE DECIMAL(10,4),

EFFECTIVE_START_DATE DATE

);INSERT INTO EXCHANGE_RATES VALUES ('INR','USD','0.014','2020-12-31');

INSERT INTO EXCHANGE_RATES VALUES ('INR','USD','0.015','2021-01-02');

INSERT INTO EXCHANGE_RATES VALUES ('GBP','USD','1.32','2020-12-20');

INSERT INTO EXCHANGE_RATES VALUES ('GBP','USD','1.30','2021-01-01');

INSERT INTO EXCHANGE_RATES VALUES ('GBP','USD','1.35','2021-01-16');

commit;

 

SELECT

FROM_CURRENCY,

TO_CURRENCY,

EXCHANGE_RATE,

EFFECTIVE_START_DATE,

NVL(LEAD(EFFECTIVE_START_DATE-1) OVER(PARTITION BY FROM_CURRENCY ORDER BY EFFECTIVE_START_DATE),'9999-12-31') AS EFFECTIVE_END_DATE

FROM EXCHANGE_RATES;

 

WITH ER AS(

SELECT

FROM_CURRENCY,

TO_CURRENCY,

EXCHANGE_RATE,

EFFECTIVE_START_DATE,

NVL(LEAD(EFFECTIVE_START_DATE-1) OVER(PARTITION BY FROM_CURRENCY ORDER BY EFFECTIVE_START_DATE),'9999-12-31') AS EFFECTIVE_END_DATE

FROM EXCHANGE_RATES

)

SELECT

a.SALES_DATE,

SUM(a.SALES_AMOUNT*b.EXCHANGE_RATE) AS TOTAL_SALES_USD

FROM  SALES a JOIN ER b

ON a.CURRENCY = b.FROM_CURRENCY

AND a.SALES_DATE BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE

GROUP BY SALES_DATE;