R Systems SQL Interview Question - Find the Currency Exchange rate at beginning and ending of month
Вставка
- Опубліковано 15 вер 2024
- One of the SQL questions recently asked in R Systems interview.
Given us exchange_rates table, We need to Find the Currency Exchange rate at beginning and ending of month.
We can solve this scenario by generating row_number().
Let us first create exchange_rates table
CREATE TABLE exchange_rates (
currency_code VARCHAR(3),
date DATE,
currency_exchange_rate DECIMAL(10, 2)
)
Insert the records
INSERT INTO exchange_rates (currency_code, date, currency_exchange_rate) VALUES
('USD', '2024-06-01', 1.20),
('USD', '2024-06-02', 1.21),
('USD', '2024-06-03', 1.22),
('USD', '2024-06-04', 1.23),
('USD', '2024-07-01', 1.25),
('USD', '2024-07-02', 1.26),
('USD', '2024-07-03', 1.27),
('EUR', '2024-06-01', 1.40),
('EUR', '2024-06-02', 1.41),
('EUR', '2024-06-03', 1.42),
('EUR', '2024-06-04', 1.43),
('EUR', '2024-07-01', 1.45),
('EUR', '2024-07-02', 1.46),
('EUR', '2024-07-03', 1.47)
For more SQL interview questions. Check out our playlist.
• SQL Interview Questions
Contact us:
info@cloudchallengers.com
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers
My Solution: with cte1 as(
select * from (
select *,row_number() over(partition by currency_code,extract(month from date) order by date) rn1,
concat(currency_code, "_", extract(year from date), "_", extract(month from date)) currency_code_year_month
from exchange_rates) a
where rn1 = 1
),
cte2 as (
select * from (
select *,row_number() over(partition by currency_code,extract(month from date) order by date desc) rn2,
concat(currency_code, "_", extract(year from date), "_", extract(month from date)) currency_code_year_month
from exchange_rates) b
where rn2 = 1
)
select cte1.currency_code_year_month, cte1.currency_exchange_rate, cte2.currency_exchange_rate
from cte1 join cte2 on cte1.currency_code_year_month = cte2.currency_code_year_month;
Please explain the steps for the solution/write steps in Excel before jumping to solution. It will have a clear idea . By following the steps one can try from his/her side before seeing the solution.
@chandanpatra1053 sure. Thanks for the suggestion
Superb explanation
Excellent bro thnk u❤
@mohammedvahid5099, Thanks for your support.
❤mind blowing
Its so helpful ❤
@prajithkumar3253, Thanks for your feedback
with cte as(
select CONCAT(currency_code,SUBSTRING(date,1,7)) as value,currency_exchange_rate FROM exchange_rates
),cte1 as(
select value,currency_exchange_rate,LEAD(currency_exchange_rate)OVER(PARTITION BY value) as x1,
LAG(currency_exchange_rate)OVER(PARTITION BY value) as x2 FROM cte
),cte2 as(
select * FROM cte1 where x1 is null or x2 is null ORDER BY value DESC
),cte3 as(
select cte2.*,LEAD(currency_exchange_rate)OVER(partition by value) as x3 FROM cte2
),cte4 as(
select cte3.* FROM cte3 where x3 is not null
)
select value,currency_exchange_rate,x3 FROM cte4;
Can it be done by first_value and last_value window function?
@rishitiwari9974, yes we can achieve using first_value and last_value window function also.
my solution in MSSQL DB :
with cte1 as
(
Select *, FORMAT(date,'yyyy') as years, FORMAT(date,'MM') as months from exchange_rates
)
Select CONCAT(currency_code,'_',years,'_',months) as currency_code_year_month
,min(currency_exchange_rate) as startofmonth
, max(currency_exchange_rate) as endofmonth
from cte1
group by CONCAT(currency_code,'_',years,'_',months)
@vijaygupta7059, we need to find exchange_rate at beginning of month and ending of month, Not min and max exchange rate. Exchange rate may also drop in between somewhere of the month.
('USD', '2024-06-01', 1.20),
('USD', '2024-06-02', 1.21),
('USD', '2024-06-03', 1.19),
('USD', '2024-06-04', 1.23
In this example, as per your query it will take min exchange rate, but not the exchange_rate at beginning of month
Yes bro gud to clarified ❤@@CloudChallengers
can we not use first and last?
@siddharthchoudhary103, yes we can achieve using first_value and last_value window function also.
In this particular question as we can see the date and currency_exchange_rate columns are in asc order itself can we not use simple group by to do it. I tried the below code in postgresql and it is working. Can you tell me if my solution is wrong.
select
currency_code || '_' || to_char(date,'yyyy_mm') as currency_code_year_month,
min(currency_exchange_rate) as currency_exchange_rate_beginningofmonth,
max(currency_exchange_rate) as currency_exchange_rate_endofmonth
from exchange_rates
group by currency_code,to_char(date,'yyyy_mm')
order by currency_code,to_char(date,'yyyy_mm')
one issue is date format will include zero infront of month number '06 ' or '07'...hence month() has to be used for concatenation. rest query will work
we need to find exchange_rate at beginning of month and ending of month, Not min and max exchange rate. Exchange rate may also drop in between somewhere of the month.
('USD', '2024-06-01', 1.20),
('USD', '2024-06-02', 1.21),
('USD', '2024-06-03', 1.19),
('USD', '2024-06-04', 1.23
In this example, as per your query it will take min exchange rate, but not the exchange_rate at beginning of month.
Here is my solution in MySQL using CTE and WINDOW functions : FIRST_VALUE() and LAST_VALUE()
WITH cte AS(
SELECT currency_code,CONCAT(currency_code,'-',YEAR(date),'-', MONTH(date)) AS currency_yearmonth, currency_exchange_rate FROM exchange_rates)
SELECT DISTINCT currency_yearmonth,
FIRST_VALUE(currency_exchange_rate) OVER(PARTITION BY currency_yearmonth ORDER BY currency_yearmonth) AS first_day_rate,
LAST_VALUE(currency_exchange_rate) OVER(PARTITION BY currency_yearmonth ORDER BY currency_yearmonth ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS last_day_rate
FROM cte;