Table students(id int primary key, name text, branch text, cgpa numeric(3,1)).
For every branch that has at least 3 students, return branch, the number of students as students, and the average CGPA rounded to 2 decimals as avg_cgpa. Order by avg_cgpa descending, then branch.
Constraints
- Write one SELECT statement for PostgreSQL 16.
- Output columns and row order must match exactly.
Input and output
- Input
- The setup script in the test case creates and fills the tables described above.
- Output
- Columns: branch, students, avg_cgpa.
Example
Input
create table students (id int primary key, name text not null, branch text not null, cgpa numeric(3,1) not null);
insert into students values (1,'A','CSE',9.0),(2,'B','CSE',8.0),(3,'C','CSE',7.5),(4,'D','ECE',9.9),(5,'E','ECE',9.7),(6,'F','ME',6.0),(7,'G','ME',7.0),(8,'H','ME',8.0);Output
CSE|3|8.17
ME|3|7.00ECE has only two students, so it is left out. CSE averages 8.17 and ME 7.00.
Show hints
- WHERE filters rows before grouping; HAVING filters groups after.
- round(numeric, 2) keeps two decimal places.
Show the explanation
GROUP BY branch makes one group per branch; count(*) and avg(cgpa) are computed per group. The "at least 3" rule is about groups, so it belongs in HAVING count(*) >= 3 — WHERE cannot see aggregates. round(avg(cgpa), 2) returns a numeric with two decimals. Ordering by the alias avg_cgpa is allowed in ORDER BY.
Original UniCircle question. Last reviewed 22 Oct 2026.