get all students who has got more marks than the average marks of that class

I got two tables below called tbl_student where we store students’ data like id, name, class, roll no. and age, and tbl_marks where we store marks scored in an exam. –

CREATE TABLE IF NOT EXISTS public.tbl_student
(
    student_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    student_name varchar(255) NOT NULL,
    student_class integer,
    student_roll_no integer,
    age integer,
    CONSTRAINT tbl_student_pkey PRIMARY KEY (student_id)
)

and

CREATE TABLE IF NOT EXISTS public.tbl_marks
(
    marks_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    student_id integer NOT NULL,
    subject varchar(255),
    marks integer,
    CONSTRAINT tbl_marks_pkey PRIMARY KEY (marks_id)
)

And these are some data for the respective tables:

INSERT INTO
    public.tbl_student (student_name, student_class, student_roll_no, age)
VALUES
    ('John Doe',5,1, 12),
    ('Clark Keny', 5,2, 10),
    ('Ross Barkley', 5, 3, 11),
    ('Frank Lampard', 5, 4, 9),
    ('John Terry', 5, 5, 10),
    ('Peter Parker',6,1, 13),
    ('Tony Stark', 6,2,11),
    ('Bruce Wayne', 6, 3,14),
    ('Johnny Depp', 6, 4,11),
    ('Jackie Chan',7,1,15),
    ('John Wick', 7,2,14),
    ('Indiana Jones', 7, 3,11),
    ('Halley Berry', 7, 4,12),
    ('Jane Doe', 7, 5,15),
    ('Martha Kent', 7, 6,13);

and

INSERT INTO
    public.tbl_marks (student_id, subject, marks)
VALUES
    (1, 'Math' , 79),
    (1, 'Science' , 64),
    (2, 'Math' , 69),
    (2, 'Science' , 72),
    (3, 'Math' , 30),
    (3, 'Science' , 50),
    (4, 'Math' , 77),
    (4, 'Science' , 72),
    (5, 'Math' , 84),
    (5, 'Science' , 88),
    (6, 'Math' , 36),
    (6, 'Science' , 54),
    (7, 'Math' , 55),
    (7, 'Science' , 54),
    (8, 'Math' , 66),
    (8, 'Science' , 78),
    (9, 'Math' , 43),
    (9, 'Science' , 20),
    (10, 'Math' ,87 ),
    (10, 'Science' , 92),
    (11, 'Math' , 68),
    (11, 'Science' , 82),
    (12, 'Math' , 78),
    (12, 'Science' , 93),
    (13, 'Math' , 20),
    (13, 'Science' , 22),
    (14, 'Math' , 40),
    (14, 'Science' , 42),
    (15, 'Math' , 52),
    (15, 'Science' , 62);

Now, I need to get all students in each class who got more marks than the average marks in their respective classes.

I tried it using the RANK() function with the PARTITION BY clause.

Answer

try this :

SELECT a.*
  FROM
     ( SELECT s.*
            , m.subject
            , m.marks
            , avg(m.marks) OVER (PARTITION BY m.subject, s.student_class) AS marks_avg
         FROM tbl_marks AS m
        INNER JOIN tbl_student AS s
           ON s.student_id = m.student_id
    ) AS a
WHERE a.marks > a.marks_avg

see the result in dbfiddle.

see the manual about the window functions :

https://www.postgresql.org/docs/current/tutorial-window.html https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-WINDOW https://www.postgresql.org/docs/current/functions-window.html

and also about the aggregate functions which can be used as window functions :

https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES https://www.postgresql.org/docs/current/functions-aggregate.html