SQL Interview Question | 42 | #dataengineering | #dataanalyst | #powerbi | #sql #dataengineer

If you like this video
please like, share & subscribe and comment down below..
if you have any suggestion or doubt in this video.

Script:

create table accounts(account_id int, income int)

insert into accounts values(1,90000)
insert into accounts values(2,17000)
insert into accounts values(3,22000)
insert into accounts values(4,13000)
insert into accounts values(5,50000)
insert into accounts values(6,56000)
insert into accounts values(7,19000)

[rampavan]

1)avg i have calculated from the data itself

with cte as
(
select *, avg(income)over() as avgg from accounts1
),
cte2 as
(
select *,
case
when income > avgg then 'high'
when income = avgg then 'avgg' else 'low' end as ranges
from cte
)
select ranges, count(ranges) from cte2
group by ranges




2)

select ranges, count(ranges) counts from
(
select *,
case
when income > 40000 then 'high'
when income between 20000 and 40000 then 'avgg'
when income < 20000 then 'low' end as ranges
from accounts1
)x
group by ranges
order by counts



Suggestion:- Try to Avoid the use of SELECT statements whenever possible to prevent potential performance issues.

[akhilsingh]

My approach:

with cte as (
select *, case when income<20000 then 'low salary'
when income>20000 and income<40000 then 'Average salary'
when income>40000 then 'High salary'end as 'statuss'
from accounts)
select statuss, count(statuss) as accounts_count
from cte
group by statuss

[gqcglsf]

select 'Low Salary' as category, count(*) cnt from Accounts where income < 20000
UNION ALL
select 'Avg Salary' as category, count(*) cnt from Accounts where income between 20000 and 40000
UNION ALL
select 'High Salary' as category, count(*) cnt from Accounts where income > 40000

[jagadeesansrinivasan]

with cte as (
select *, case when income < 20000 then 'low_salary'
when income between 20000 and 40000 then 'avg_salary' else 'high_salary' end as category
from accounts )
select category, count(category) as accounts_count
from cte
group by category
order by category ;

[tanmaykapil]

with cte as(
select account_id, income,
case when income<20000 then 'low salary'
when income >20000 and income <40000 then 'avg salary'
else 'high salary' end as category
from accounts)

select category, count(*) as account_count from cte
group by category
order by
case when category='low salary' then 1
when category='avg salary' then 2
else 3 end;


my approach your videos are like daily leetcode challenges please post on a daily basis please

[MathanRJ-pc]

with temp as
(select a.*, case
when income < 20000 then "low Salary"
when income between 20000 and 40000 then "Average Salary"
when income > 40000 then "High Salary" end as category
from accounts a
)
select category, count(account_id) as accounts_count
from temp
group by category
order by accounts_count

[Sakthi-wj]

select category, count(*) as accounts_count from (
select *,
case when income<20000 then "low salary"
when income between 20000 and 40000 then "average salary"
else "high salary"
end as category
from accounts)a
group by category;

[yousfimohamed]

WITH cte AS (
SELECT *,
CASE
WHEN income < 20000 THEN 'Low salary'
WHEN income > 40000 THEN 'High salary'
ELSE 'Avg salary'
END AS category
FROM accounts
)
SELECT category,
COUNT(category) AS accounts_count
FROM cte
GROUP BY category

[Shinykip]

SELECT
'low salary' as category,
-- Up to, but not including, 20000. Or [, 20000)
SUM(CASE WHEN income < 20000 THEN 1 ELSE 0 END) AS account_cnt FROM accounts
UNION ALL
SELECT
'avg salary' as category,
-- From 20000 up to, but not including, 40000. Or [20000, 40000)
SUM(CASE WHEN 20000 <= income AND income < 40000 THEN 1 ELSE 0 END) AS account_cnt FROM accounts
UNION ALL
SELECT
'high salary' as category,
-- From 40000 up. Or [40000, )
SUM(CASE WHEN 40000 <= income THEN 1 ELSE 0 END) AS account_cnt FROM accounts;