EPAM SQL Coding Test Interview Questions | Aam vs Mentos Life

[@ankitbansal6]

Master SQL with my zero to hero SQL for the analytics course :
www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english
Use code ILOVESQL at checkout.

[@varunas9784]

Thank you Ankit like always, great!
Here's my attempt on SQL server:
==========================================
select experience,
COUNT(case when (isnull([sql], 0) + isnull([algo], 0) + isnull([bug_fixing], 0))%100 = 0
then 1
end) [Max score students],
COUNT(1) [total students]
from assessments
group by experience
==========================================

[@shreyagupta.1203]

Another helpful video!
This is my approach
select
experience,
sum(
case when (sql is null or sql = 100) and
(algo is null or algo = 100) and
(bug_fixing is null or bug_fixing = 100) then 1 else 0 end) as max_score_students,
count(id) as total_students
from
assessments
group by experience
order by experience;

[@nikhilpurwar697]

My first attempt:
with cte as(
select id,experience,case when sql is Null then 100 else sql end as sql1,
case when algo is Null then 100 else algo end as algo1,
case when bug_fixing is Null then 100 else bug_fixing end as bug_fixing1 from assessments),cte2 as(
select experience ,case when (sql1+algo1+bug_fixing1)=300 then 1 else 0 end as totalmarks_flag
from cte)
select experience,sum(totalmarks_flag) as max_total,count(experience) as total_students from cte2 group by experience

[@saralavasudevan5167]

Great solve Ankit! Please make more interview questions for SQL and Python as well :).
This was my approach:
select
experience,
SUM(case when
((sql = 100 and algo = 100 and bug_fixing = 100)
or (sql = 100 and algo = 100)
or (sql = 100 and bug_fixing = 100)
or (algo = 100 and bug_fixing = 100))
then 1 else 0 end)
as max_scores,
count(id) as total_assesments
from assessments
GROUP BY experience
order by 1

[@rishabhralli9151]

with cte as(
select *,
case when sql=100 or null then 1 else 0 end as sql_score,
case when algo=100 or null then 1 else 0 end as algo_score,
case when bug_fixing =100 or null then 1 else 0 end as bug_fixing_score
from assessments)
select experience,count(*) as total_students,sum(case when sql_score=1 and algo_score=1 and bug_fixing_score=1 then 1 else 0 end) as max_score
from cte group by 1
my solution is much shorter and dynamic

[@keshavkumar7769]

First using a group-by we will find a count of users for each experience level.
Then using another cte query we will try to find the perfect user using below logic
Select user, expe, Case when ( coalesce(SQL, 0)+ coalesce(algo,0)+ coalesce (bug,0)
)% 100 = 0 then 1 else 0
end as perfect
From table
Then in the 3rd cte query we will find out the count of the perfect user for each Exp using the above cte.
At last we will join 1st and 3rd cte

[@shivshankarkumar7446]

Optimize Solution:
SELECT
experience AS exp,
SUM(CASE
WHEN (sql IS NULL OR sql = 100)
AND (algo IS NULL OR algo = 100)
AND (bug_fixing IS NULL OR bug_fixing = 100) THEN 1 ELSE 0
END) AS max,
COUNT(*) AS count
FROM assessments GROUP BY experience
ORDER BY exp DESC;

[@HimanshuSingh-lk2my]

Hi Ankit sir. One feedback
I have taken your recorded course for SQL. I am unable to form complex query where to use CTE and where to put order by and other commands. Can you make a video how to structure a query

[@ankitbansal6]

Practice questions from my complex SQL playlist