SQL Quest › SQL Interview Questions › Aggregation & Grouping

Movie Title Initial Letter Histogram (SUBSTR)

EasyFreeQuerying BasicsAggregation & GroupingString Functions

A library-style index wants a histogram of movie titles by their first letter. Group movies by UPPER(SUBSTR(title, 1, 1)) so 'a' and 'A' bucket together.

Show initial and movie_count. Order by initial.

The UPPER is a defensive normalization — most titles start with capitals, but mixed casing in source data is the kind of subtle bug that shows up in production after a content team imports something messy.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect". Free accounts get 10 free challenge solves, and this can be one of them.

Schema

movies

id	title	year	genre	rating	votes	revenue_millions	runtime	director
1	Guardians of the Galaxy	2014	Action	8.1	757074	333.13	121	James Gunn
2	Prometheus	2012	Adventure	7	485820	126.46	124	Ridley Scott
3	Split	2016	Horror	7.3	157606	138.12	117	M. Night Shyamalan

Expected output: A → 2, B → 1

Hint

The hint for this one spells out most of the query, so it stays in the editor. Reach for SELECT, GROUP BY, SUBSTR, and open the hint there if you stall.

Concepts

SELECT GROUP BY SUBSTR String Functions

Practise the topic: SQL practice questions · GROUP BY exercises · String function practice

Read the concept: SQL GROUP BY tutorial

Movie Title Initial Letter Histogram (SUBSTR)

Schema

Hint

Concepts

Related questions