SQL Interview Question | 21 |

[@VishalSingh-ul2ko]

Great explaining..Your step-by-step breakdown made it easy to understand the logic and purpose behind each part..

[@codinglake]

thanks.

[@Harishsatya-b4b]

Well Explained 👍

[@codinglake]

Thank you 🙂

[@narendra742]

Nicely explained

[@codinglake]

Thanks.

[@srikanth2610]

Simply you can filter the date by state = 'approved'

[@codinglake]

But how do you sum multiple approved transactions and transaction counts.

[@SundeepKumarModiboina]

Just a small change, used IIF insted of Case when
WITH cte AS ( SELECT *,FORMAT(trans_date, 'yyy-MM') as month FROM transactions)
SELECT month, country, count(*) AS trans_count, SUM(amount) AS trans_total_Amount, SUM(IIF(state = 'Approved', 1, 0)) AS approved_count, SUM(IIF(state = 'Approved', amount, 0)) AS approved_total_amount
FROM cte
GROUP BY month, country

[@codinglake]

Nice 👍

[@user-gq6cg3ls7f]

another approach
with cte as(
select FORMAT(trans_date,'yyyy-MM') as month,country, count(*) cnt, sum(amount) trans_total_amount
from transactions_thus
group by FORMAT(trans_date,'yyyy-MM') , country
), cte2 as(
select FORMAT(trans_date,'yyyy-MM') as month2, count(*) approved, amount from transactions_thus where state='approved'
group by amount, FORMAT(trans_date,'yyyy-MM')
)
select c1.*, approved, amount from cte c1 inner join cte2 c2
on c1.month = c2.month2

[@codinglake]

Nice one 👍

[@rampavan9521]

thumbnail output is different than actual output (😇)
select
to_char(trans_date , 'yyyy-mm'),country,
count(state),sum(case when state = 'approved' then 1 else 0 end),sum(amount) from transactions10
group by to_char(trans_date , 'yyyy-mm'),country

[@codinglake]

Due to space issues.

[@jayaprakashreddyittech]

with cte AS (SELECT *,DATE_FORMAT(trans_date,'%y-%m') AS months
,case when state='approved' then 1 ELSE 0 end AS approved_tx_count,
case when state='approved' then amount ELSE 0 end AS approved_tx_amount FROM transactions)
SELECT months,country,COUNT(*) AS tx_count,SUM(amount) AS tx_amount,
SUM(approved_tx_count),
max(approved_tx_amount) FROM cte GROUP BY months,country ;

[@codinglake]

Good 👍

[@vishnu1619]

U made complicated😢

[@codinglake]

Try once then see it's easy.

[@wonderoverload0]

This is literally the way one will write it. What's complicated in it?