SQL Interview Problem - Solution - (Part - XX) |
ฝัง
- เผยแพร่เมื่อ 14 ต.ค. 2024
- #education #sqlfordataengineer #sqlserver #interviewquery #dataanalyst
Here are My profiles that will definitely help your preparation for data analyst or data engineer roles.
Medium: / mahendraee204
Github: github.com/mah...
Table Create and insert statements:
-------------------------------------------------------
create table numbers (
id int primary key,
num int
)
insert into numbers values (1, 3),(2, 8),(3, 2),
(4, 10),(5, 14),(6, 3),(7, 20),(8, 15),(9, 4),(10, 8),
(11, 6),(12, 3),(13, 10),(14, 22),(15, 3),(16, 8)
WITH cte AS (
SELECT num,
ROW_NUMBER() OVER (ORDER BY num) AS row_num
COUNT(*) OVER() AS total_count
FROM numbers
),
mean_cal AS (
SELECT avg(num) AS mean
FROM numbers
)
mode_cal AS (
SELECT num AS mode
FROM numbers
GROUP BY num
ORDER BY count(*) DESC
LIMIT 1
)
SELECT
(SELECT mean FROM mean_cal) AS mean,
(SELECT mode FROM mode_cal) AS mode,
AVG(num) AS median
FROM cte
WHERE row_num IN ((total_count + 1) / 2, (total_count + 2) / 2);