FREE AI-Enhanced Online PostgreSQL Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE myt 
(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    letter CHAR(1),
    date DATE
);

INSERT INTO myt (id, name, letter, date) 
VALUES
(1, 'Alice', 'C', '2023-01-28'),
(2, 'Alice', 'B', '2023-02-28'),
(3, 'Alice', 'A', '2023-03-28'),
(4, 'Bob', 'B', '2023-01-09'),
(5, 'Bob', 'C', '2023-02-09'),
(6, 'Bob', 'B', '2023-03-09'),
(7, 'Charlie', 'B', '2023-01-19'),
(8, 'Charlie', 'A', '2023-02-19'),
(9, 'Charlie', 'A', '2023-03-19'),
(10, 'Charlie', 'A', '2023-04-19'),
(11, 'Charlie', 'A', '2023-05-19'),
(12, 'David', 'B', '2023-01-05'),
(13, 'David', 'C', '2023-02-05'),
(14, 'David', 'A', '2023-03-05'),
(15, 'David', 'B', '2023-04-05'),
(16, 'David', 'B', '2023-05-05'),
(17, 'David', 'A', '2023-06-05'),
(18, 'Emma', 'B', '2023-01-15'),
(19, 'Emma', 'A', '2023-02-15'),
(20, 'Emma', 'A', '2023-03-15'),
(21, 'Emma', 'A', '2023-04-15'),
(22, 'Emma', 'A', '2023-05-15'),
(23, 'Frank', 'B', '2023-01-06'),
(24, 'Frank', 'A', '2023-02-06'),
(25, 'Frank', 'A', '2023-03-06'),
(26, 'Frank', 'A', '2023-04-06'),
(27, 'Grace', 'A', '2023-01-27'),
(28, 'Grace', 'B', '2023-02-27'),
(29, 'Grace', 'B', '2023-03-27'),
(30, 'Henry', 'B', '2023-01-31'),
(31, 'Henry', 'A', '2023-03-03'),
(32, 'Henry', 'A', '2023-03-31'),
(33, 'Henry', 'A', '2023-05-01'),
(34, 'Henry', 'A', '2023-05-31'),
(35, 'Isabel', 'B', '2023-01-20'),
(36, 'Isabel', 'C', '2023-02-20'),
(37, 'Isabel', 'A', '2023-03-20'),
(38, 'Isabel', 'A', '2023-04-20'),
(39, 'Isabel', 'C', '2023-05-20'),
(40, 'Jack', 'B', '2023-01-30'),
(41, 'Jack', 'C', '2023-03-02'),
(42, 'Jack', 'A', '2023-03-30'),
(43, 'Jack', 'B', '2023-04-30'),
(44, 'Jack', 'B', '2023-05-30'),
(45, 'Jack', 'C', '2023-06-30'),
(50, 'Koala', 'B', '2023-01-01'),
(51, 'Koala', 'B', '2023-02-01'),
(52, 'Koala', 'B', '2023-03-01'),
(53, 'Koala', 'B', '2023-04-01');

WITH RankedGrades AS 
(
    SELECT 
        name,
        letter,
        date,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY date) as grade_sequence
    FROM 
        myt
),
BStarters AS 
(
    SELECT DISTINCT name, letter first_letter
    FROM RankedGrades
    WHERE grade_sequence = 1
),
FirstASequence AS 
(
    SELECT
        r.name, b.first_letter, r.letter,
        MIN(r.grade_sequence) as first_a_sequence
    FROM 
        RankedGrades r
    JOIN
        BStarters b ON r.name = b.name
    GROUP BY 
        r.name, b.first_letter, r.letter
),
FirstADetails AS 
(
    SELECT 
        r.name,
  		fas.first_letter,
  		r.letter as first_a_letter,
        r.date as first_a_date,
        fas.first_a_sequence as a_sequence_number
    FROM 
        RankedGrades r
    JOIN 
        FirstASequence fas ON r.name = fas.name
    WHERE 
        r.grade_sequence = fas.first_a_sequence
),
SubsequentPerformance AS
(
    SELECT 
        f.name, f.first_letter, f.first_a_letter,
        CASE WHEN MAX(CASE WHEN r.letter != f.first_a_letter THEN 1 ELSE 0 END) = 0 
             THEN 'Pure '||f.first_a_letter
             ELSE 'Mixed Grades'
        END as grade_pattern
    FROM 
        FirstADetails f
    JOIN 
        RankedGrades r ON f.name = r.name
    WHERE 
        r.grade_sequence > f.a_sequence_number
    GROUP BY 
        f.name, f.first_letter, f.first_a_letter
),
Summary AS 
(
    SELECT 
        first_letter, first_a_letter, grade_pattern,
        COUNT(*) as student_count,
        SUM(COUNT(*)) OVER (partition by first_letter, first_a_letter) as total_students
    FROM 
        SubsequentPerformance
    GROUP BY 
        first_letter, first_a_letter, grade_pattern
)
SELECT 
    first_letter "Starting with", first_a_letter "entering a sequence of", grade_pattern "finished as",
    student_count,
    ROUND(CAST(student_count AS FLOAT) / total_students * 10000) / 100 as percentage -- Adapt to PostgreSQL which doesn't have round(x, precision).
FROM 
    Summary
ORDER BY 
    first_letter desc, first_a_letter, student_count DESC;

Starting with	entering a sequence of	finished as	student_count	percentage
C	B	Mixed Grades	1	100
C	C	Mixed Grades	1	100
B	A	Pure A	4	57.14
B	A	Mixed Grades	3	42.86
B	B	Mixed Grades	8	88.89
B	B	Pure B	1	11.11
B	C	Mixed Grades	4	100
A	A	Mixed Grades	1	100
A	B	Pure B	1	100

CREATE TABLE
INSERT 0 49