A SQL face question

This interview question is based on the statistical analysis of the bug continued (a) SQL-based Bug statistical method in the 3-2 section of the discussion abstract.

Title: Suppose a table has an AC two column, how to find various C in the number of different a, and list the corresponding C value, and find out each C under a maximum of a value and number? requires an SQL statement

As an example:

C A
-----
C1 A1
C1 A2
C1 A3
C2 A1
C2 A2
C2 A3
C2 A1
C3 A2
C3 A3
C3 A1
C3 A2
C3 A3
C3 A1
C3 A2
C3 A3

C1, the number of A1, A2 and A3 is 1.
C2, the number of A1, A2, and A3 is 2, 1, 1
C3, the number of A1, A2, and A3 is 2, 3, 3

So when C1, the most is A1, A2, A3
So when C2, the most is A1
So when C3, the most is A2, A3

The final output table is

C A Num

----------
C1 A1 1
C1 A2 1
C1 A3 1
C2 A1 2
C3 A2 3
C3 A3 3

Answer:

A SQL face question