SQL Interview Questions and answers Part 22 | Amazon SQL Interview Question And Answer Part-6

SQL Interview Questions and answers Part 22 | Amazon SQL Interview Question And Answer Part-6

Input :- There are two table. First table name is Sales_Table. Second Table name is ExchangeRate_Table. As and when exchange rate changes, a new row is inserted in the ExchangeRate table with a new effective start date.

Problem Statements :- Write SQL to get Total sales amount in USD for each sales date as shown below :-

Follow me on Social Networking Sites :
*Twitter:
*Instagram :

Table and Insert Scripts:

Create Table Sales_Table(
Sales_Date Date,
Sales_Amount Bigint,
Currency Varchar(10)
)

INSERT INTO Sales_Table Values ('2020-01-01',500,'INR');
INSERT INTO Sales_Table Values ('2020-01-01',100,'GBP');
INSERT INTO Sales_Table Values ('2020-01-02',1000,'INR');
INSERT INTO Sales_Table Values ('2020-01-02',500,'GBP');
INSERT INTO Sales_Table Values ('2020-01-03',500,'INR');
INSERT INTO Sales_Table Values ('2020-01-17',200,'GBP');

CREATE TABLE [dbo].[ExchangeRate_Table](
[Source_Currency] [varchar](10) ,
[Target_Currency] [varchar](10),
[Exchange_Rate] [float] ,
[Effective_Start_Date] [date]
)

INSERT [dbo].[ExchangeRate_Table] VALUES ('INR','USD', 0.014,'2019-12-31')
INSERT [dbo].[ExchangeRate_Table] VALUES ('INR','USD', 0.015,'2020-01-02')
INSERT [dbo].[ExchangeRate_Table] VALUES ('GBP','USD', 1.32, '2019-12-20')
INSERT [dbo].[ExchangeRate_Table] VALUES ('GBP','USD', 1.3, '2020-01-01' )
INSERT [dbo].[ExchangeRate_Table] VALUES ('GBP','USD', 1.35, '2020-01-16')

#AmazonInterview #AmazonDataEngineer #SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sqlInterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #sqlTechnicalInterviewQuestions #SQLforETLTesters #CommonSqlInterviewQuestions

[prajjwaljaiswal]

Please try to avoid using lead() lag() and other complex functions. Please try to make solutions from fundamentals.

[rishivarahagiri]

I have got the same question today, Thank You !

[mananshah]

with x as (
select
*,
lead(date_add(effective_start_date, interval -1 day), 1, '9999-12-31') over(partition by source_currency order by source_currency) as effective_end_date
from exchangerate_table
)
select sales_date,
as total_sales_amount
from sales_table
left join x
on currency=source_currency
and sales_date between effective_start_date and effective_end_date
group by 1;
This was my solution on similar lines actually.

[bishwarup]

Possible simpler solution:

with cte as (
select
a.*
, b.*
, round(a.Sales_Amount * b.Exchange_Rate, 2) as effective_price
from Sales_Table a
left join ExchangeRate_Table b
on a.Sales_Date >= b.Effective_Start_Date
and a.Currency = b.Source_Currency),
cte2 as(
select
Sales_Date
, case when effective_start_date = max(effective_start_date) over(partition by Sales_Date, Currency) then effective_price else 0 end as final_price
from cte)
select
Sales_Date
, sum(final_price) as Total_amt_USD
from cte2
group by 1
;

[NhuTran-jwte]

First of all, thanks for your good questions. But I also dont know the use of LEAD in your command. I dont use "Lead" in my SQL and it doesnt have purple letter like u.

[TheBala]

Sir, any specific reason for using order by sales_currency ? Partition by sales_currency is fine. order by Effective_start_date should work right?

[vinaykumarramagiri]

Hi sir 👋👋👋👋👋👋 I gave my interview with *Amazon Aug 25th for the Digital content Asssociate* then there is no Mail. *Does they send Regret Mail* ?

[TheEsisia]

Solution without LEAD function:

SELECT Sales_Date, SUM(Sales_Amount * Exchange_Rate) FROM Sales_Table s
LEFT JOIN ExchangeRate_Table e1
ON s.Currency = e1.Source_Currency
AND e1.Effective_Start_Date =
(select MAX(e2.Effective_Start_Date) FROM ExchangeRate_Table e2 WHERE s.Currency = e2.Source_Currency
AND s.Sales_Date >= e2.Effective_Start_Date)
GROUP BY Sales_Date

[alextsang]

question: what does the ORDER BY souce_currecy do? Is it okay to remove this ?

[saikatde]

with cte as
(
select a.Sales_Date, a.Sales_Amount, a.Currency, b.Exchange_Rate, b.Effective_Start_Date
from Sales_Table as a inner join ExchangeRate_Table as b on a.Currency = b.Source_Currency
and a.Sales_Date >= b.Effective_Start_Date
)
select a.Sales_Date, as [Total Sales Amount in USD] from
(
select a.*, row_number() over(partition by sales_date, Sales_amount, Currency order by effective_start_date desc) as seq
from cte as a
) as a
where a.seq = 1
group by a.Sales_Date;

[RahulP]

Select Sales_Date, sum(Total) as Net from (

select Main.*, ROW_NUMBER () over (partition by Main.Sales_Date, Source_Currency order by Main.Effective_Start_Date desc) as RN from
(
select Sales_Date, Effective_Start_Date, Source_Currency, Target_Currency, Sales_Amount, Exchange_Rate, (Sales_Amount*Exchange_Rate) as Total
from T1, T2
where T1.Sales_Date >= T2.Effective_Start_Date
and T1.Currency = T2.Source_Currency
) as Main) as sub
where RN = 1 group by Sales_Date

[YASHKUMARJAIN]

MYSQL :
with cte as
(select s.*, e.*, round(e.exchange_rate* sales_amount, 1) as total from Sales_Table s join ExchangeRate_Table e
on s.currency = e.source_Currency
and s.sales_date >= Effective_Start_Date)

/* now just refine effective_start_date */

select sales_date, sum(total)
from cte
where (sales_date, currency, effective_start_date) in (select sales_date, currency, max(effective_start_date) from cte group by sales_date, currency)
group by sales_date

[akashgoel]

just the order of execution is different, rest it is mostly same.

with cte_2 as (
select source_currency, target_currency, exchange_rate, effective_start_date, DATEadd(DAY, -1,
lead(effective_start_date, 1) OVER(partition by source_currency order by source_currency desc, effective_start_date)) as effective_end_date from ExchangeRate_Table
),
cte as (
select a.sales_date, a.sales_amount, b.exchange_rate --as final--, b.effective_start_date
from Sales_Table a join cte_2 b
on a.Currency = b.Source_Currency and a.sales_date >= b.effective_start_date
and a.sales_date <= isnull(b.effective_end_date, '9999-12-31')
--where a.sales_date = '2020-01-17'
)
select sales_Date, --sales_amount, exchange_rate
as total_Sales_amount_in_USD
from cte group by sales_date

[arunkumarn]

select a.Sales_Date, sum(b.Exchange_Rate*a.Sales_Amount) as DollarAmt from Sales_Table a inner join
(
select *, isnull(lead(dateadd(day, -1, effective_start_date))
over (partition by source_currency order by effective_start_date), '9999-12-31') as EndDate from ExchangeRate_Table
)b on a.Currency = b.Source_Currency and a.Sales_Date between b.Effective_Start_Date and b.EndDate
group by Sales_Date

[anirvansen]

MYSQL Answer

with base as (select t1.*, t2.* from Sales_Table t1 inner join ExchangeRate_Table t2
on t1.Currency = t2.Source_Currency and t1.Sales_Date >= t2.Effective_Start_Date
order by sales_date, currency),
exchange_values as (select distinct sales_date, sales_amount,
first_value(exchange_rate) over (partition by sales_date, sales_amount, currency order by Effective_Start_Date desc) as eff_exchange
from base)
select sales_date, round(sum(sales_amount * eff_exchange)) as total_sales_in_usd
from exchange_values group by sales_date order by sales_date;

[prajjwaljaiswal]

MySQL:
-- Complex Solution

with cte1 as
(select *
from sales_Table st
inner join exchangerate_table et
on st.currency = et.source_currency
and st.Sales_date >= et.Effective_Start_Date
order by st.currency, effective_start_date desc),
cte2 as (
select Sales_date, Currency, max(Effective_Start_Date) as eff_start_date
from cte1
group by Sales_date, Currency)

select c1.Sales_date, round(sum(c1.sales_amount*Exchange_Rate), 1)
from cte1 c1
inner join cte2 c2
on c1.sales_date = c2.sales_date
and c1.Effective_Start_Date = c2.eff_start_date
group by c1.Sales_date;