add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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)
) ;

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)
) ;

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),
15 rows affected

30 rows affected
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

student_id student_name student_class student_roll_no age subject marks marks_avg
5 John Terry 5 5 10 Math 84 67.8000000000000000
2 Clark Keny 5 2 10 Math 69 67.8000000000000000
4 Frank Lampard 5 4 9 Math 77 67.8000000000000000
1 John Doe 5 1 12 Math 79 67.8000000000000000
7 Tony Stark 6 2 11 Math 55 50.0000000000000000
8 Bruce Wayne 6 3 14 Math 66 50.0000000000000000
12 Indiana Jones 7 3 11 Math 78 57.5000000000000000
10 Jackie Chan 7 1 15 Math 87 57.5000000000000000
11 John Wick 7 2 14 Math 68 57.5000000000000000
4 Frank Lampard 5 4 9 Science 72 69.2000000000000000
2 Clark Keny 5 2 10 Science 72 69.2000000000000000
5 John Terry 5 5 10 Science 88 69.2000000000000000
8 Bruce Wayne 6 3 14 Science 78 51.5000000000000000
6 Peter Parker 6 1 13 Science 54 51.5000000000000000
7 Tony Stark 6 2 11 Science 54 51.5000000000000000
12 Indiana Jones 7 3 11 Science 93 65.5000000000000000
11 John Wick 7 2 14 Science 82 65.5000000000000000
10 Jackie Chan 7 1 15 Science 92 65.5000000000000000