How to find the champion with the highest number of selections in match in SQLlite

Question

I'm trying to do this query : find the champion with the highest number of selections in match for each champion found display their name as well as the number of times they ha\ve been selected in match .

Here are the tables:

 CREATE TABLE champions
    (
        id_champion INT PRIMARY KEY NOT NULL,
        name VARCHAR(20),
        title VARCHAR(20),
        attack INT,
        defense INT,
        magic INT,
        difficulty INT
    );
    
    CREATE TABLE players
    (
        id_player INT PRIMARY KEY NOT NULL,
        name VARCHAR(20),
        country VARCHAR(20)
    );
    
    CREATE TABLE matchs
    (
        id_match INT,
        player INT,
        champion INT,
        number INT,
        gold INT,
        team INT,
        position VARCHAR(10),
        PRIMARY KEY (id_match, player),
        FOREIGN KEY(id_match) REFERENCES matchsmeta(id_match) ON DELETE SET NULL,
        FOREIGN KEY(player) REFERENCES players(id_player) ON DELETE SET NULL,
        FOREIGN KEY(champion) REFERENCES champions(id_champion) ON DELETE SET NULL
    
    );
    
    CREATE TABLE matchsmeta
    (
        id_match INT PRIMARY KEY NOT NULL,
        time INT,
        victory INT,
        date DATE,
        game_mode VARCHAR(10)
    );

Answer 1

You have to make the count of times a champion appears in matchs table. Then you can join the champions table to get the name.

SELECT
m.champion,
c.name,
SUM(1) as num_times_picked
FROM matchs m
LEFT JOIN champions c on (m.champion = c.id_champion)
GROUP BY m.champion
ORDER BY 3 DESC