I have this table in SQL about the years in which students were studying in a school:
CREATE TABLE myt (
student_name VARCHAR(50),
student_year INT
);
INSERT INTO myt (student_name, student_year) VALUES
('john', 2010),
('John', 2011),
('John', 2012),
('John', 2019),
('John', 2020),
('alex', 2005),
('tim', 2000),
('tim', 2000),
('jack', 2020),
('jack', 2024);
student_name student_year
john 2010
John 2011
John 2012
John 2019
John 2020
alex 2005
tim 2000
tim 2000
jack 2020
jack 2024
For each student, for all years between their min year and max year - I want to find out how many years they have missed and the percent of years they have missed.
The final result should look like this:
student_name student_year total_years missed_years percent_missed
john 2010 1 0 0
john 2011 2 0 0
john 2012 3 0 0
john 2013 4 1 25
john 2014 5 2 40
john 2015 6 3 50
john 2016 7 4 57.1
john 2017 8 5 62.5
john 2018 9 6 66.7
john 2019 10 6 60
john 2020 11 6 54.5
alex 2005 1 0 0
tim 2000 1 0 0
tim 2000 1 0 0
jack 2020 1 0 0
jack 2021 2 1 50
jack 2022 3 2 66.7
jack 2023 4 3 75
jack 2024 5 3 60
I tried to do the following approach:
Here is my approach:
WITH calendar_years AS (
SELECT 2000 AS year UNION ALL
SELECT 2001 UNION ALL
SELECT 2002 UNION ALL
SELECT 2003 UNION ALL
SELECT 2004 UNION ALL
SELECT 2005 UNION ALL
SELECT 2006 UNION ALL
SELECT 2007 UNION ALL
SELECT 2008 UNION ALL
SELECT 2009 UNION ALL
SELECT 2010 UNION ALL
SELECT 2011 UNION ALL
SELECT 2012 UNION ALL
SELECT 2013 UNION ALL
SELECT 2014 UNION ALL
SELECT 2015 UNION ALL
SELECT 2016 UNION ALL
SELECT 2017 UNION ALL
SELECT 2018 UNION ALL
SELECT 2019 UNION ALL
SELECT 2020 UNION ALL
SELECT 2021 UNION ALL
SELECT 2022 UNION ALL
SELECT 2023 UNION ALL
SELECT 2024
),
student_years AS (
SELECT
student_name,
MIN(student_year) AS min_year,
MAX(student_year) AS max_year
FROM myt
GROUP BY student_name
),
student_calendar AS (
SELECT
s.student_name,
c.year
FROM student_years s
JOIN calendar_years c ON c.year BETWEEN s.min_year AND s.max_year
),
filled_years AS (
SELECT
sc.student_name,
sc.year,
CASE WHEN m.student_year IS NULL THEN 1 ELSE 0 END AS is_missing
FROM student_calendar sc
LEFT JOIN myt m ON sc.student_name = m.student_name AND sc.year = m.student_year
),
aggregated AS (
SELECT
student_name,
year,
SUM(is_missing) OVER (PARTITION BY student_name ORDER BY year) AS missed_years,
COUNT(*) OVER (PARTITION BY student_name ORDER BY year) AS total_years
FROM filled_years
)
SELECT
student_name,
year,
total_years,
missed_years,
(missed_years * 1.0 / total_years * 1.0) * 100 AS percent_missed
FROM aggregated
ORDER BY student_name, year;
The final result looks like this:
student_name year total_years missed_years percent_missed
John 2010 1 0 0.00000
John 2011 2 0 0.00000
John 2012 3 0 0.00000
John 2013 4 1 25.00000
John 2014 5 2 40.00000
John 2015 6 3 50.00000
John 2016 7 4 57.14286
John 2017 8 5 62.50000
John 2018 9 6 66.66667
John 2019 10 6 60.00000
John 2020 11 6 54.54545
alex 2005 1 0 0.00000
jack 2020 1 0 0.00000
jack 2021 2 1 50.00000
jack 2022 3 2 66.66667
jack 2023 4 3 75.00000
jack 2024 5 3 60.00000
tim 2000 2 0 0.00000
tim 2000 2 0 0.00000
Does this approach make sense for solving this problem?
Does this approach make sense for solving this problem?
Considering given limitations
Netezza has very limited SQL functions
Yes, your approach is correct.
Also, your code is well-formatted and easy to read.
Your code is slightly bloated. Personally, I'd include student_calendar and aggregated CTEs into filled_years. Here it wouldn't improve performance, so it's just a code style choice.
There are some problems with your input data:
john and John('tim', 2000) row is repeatedBecause of these, your actual output is different from expected.
I suspect these should be fixed in input data. But if the input can't be changed, then #1 can be easily fixed in code with LOWER. A fix for #2 in the code is more complex. Please leave a comment, if you need this, I'll expand the answer.
Also, note that percent_missed in expected output is rounded to 1 fractional digit. Yours isn't.