PwC SQL Interview Question - Find the percentage of Genders

One of the SQL questions recently asked in PwC interview.
Given us Employee table, We need to Find the percentage of Genders.
In order to solve this questions, we used Case Statement and GroupBy count.
Let us create table and insert data
CREATE TABLE employees_tbl (eid INT, ename VARCHAR(50), gender VARCHAR(10))
INSERT INTO employees_tbl VALUES (1, 'John Doe', 'Male'),(2, 'Jane Smith', 'Female'),
(3, 'Michael Johnson', 'Male'),(4, 'Emily Davis', 'Female'),(5, 'Robert Brown', 'Male'),
(6, 'Sophia Wilson', 'Female'),(7, 'David Lee', 'Male'),(8, 'Emma White', 'Female'),
(9, 'James Taylor', 'Male'),(10, 'William Clark', 'Male')
For more SQL interview questions. Check out our playlist.
• SQL Interview Questions
Contact us:
[e-mail]
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers

[@shashank_1180]

declare @total int
select @total=count(1) from #employees_tbl;
with cte as(
select gender,count(1)*100 /@total as perc
from #employees_tbl
group by gender
)
select max(case when gender='female' then perc end) as female
,max(case when gender='male' then perc end) as male
from cte

[@durgarajmukthasree79]

I've solved this using subquery
select round(((select count(gender) from employees_tbl
where gender='Male')/count(gender))*100) as Male_per,
round(((select count(gender) from employees_tbl
where gender='Female')/count(gender))*100) as Female_per
from employees_tbl;

[@smileymoviereview2589]

Great 👍. Appreciate your hardworking 💪

[@CloudChallengers]

@smileymoviereview2589, Thanks for your feedback

[@tummidisaikrishna]

Thanks brother and i solved this problem using CTE......
WITH CTE1 as(
SELECT count(*) as total_emp
FROM employees_tbl
),
CTE2 AS(
SELECT gender, count(*) as gender_count
FROM employees_tbl
GROUP BY gender
)
SELECT CTE2.*, ROUND((CTE2.gender_count/CTE1.total_emp)*100,0) as gender_perc
FROM CTE2, CTE1;

[@abhishekpathak7338]

Awesome but focus on optimised query

[@sudhindrab1606]

with cte as (
select gender, count(gender) over(partition by gender) * 100 as ss ,max(eid) over() as aa from employees_tbl
)
select gender,ss/aa as percentage from cte
group by gender,ss,aa

[@sabesanj5509]

Awesome brother👍

[@CloudChallengers]

@sabesanj5509, Thanks for your support.