high_scores.sql

/* What does the app's SQL look like? */

CREATE TABLE scores (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    score INTEGER
);

CREATE TABLE users (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

INSERT INTO users (name) VALUES ("Ethan");
INSERT INTO users (name) VALUES ("William");
INSERT INTO users (name) VALUES ("John");
INSERT INTO users (name) VALUES ("Jacob");
INSERT INTO users (name) VALUES ("Mac");

INSERT INTO scores (user_id, score) VALUES (1, 34232);
INSERT INTO scores (user_id, score) VALUES (1, 345232);
INSERT INTO scores (user_id, score) VALUES (1, 5232);
INSERT INTO scores (user_id, score) VALUES (1, 561225);
INSERT INTO scores (user_id, score) VALUES (2, 902);
INSERT INTO scores (user_id, score) VALUES (2, 9);
INSERT INTO scores (user_id, score) VALUES (2, 5649085);
INSERT INTO scores (user_id, score) VALUES (2, 5611185);
INSERT INTO scores (user_id, score) VALUES (3, 90002);
INSERT INTO scores (user_id, score) VALUES (3, 564985);
INSERT INTO scores (user_id, score) VALUES (3, 561895);
INSERT INTO scores (user_id, score) VALUES (4, 52);
INSERT INTO scores (user_id, score) VALUES (4, 5232);
INSERT INTO scores (user_id, score) VALUES (4, 90232);
INSERT INTO scores (user_id, score) VALUES (4, 9232);

/* Get highest score from all users. */

SELECT users.name, MAX(scores.score) FROM users
    LEFT OUTER JOIN scores
    ON users.id = scores.user_id
    GROUP BY users.name
    ORDER BY MAX(scores.score)
    DESC LIMIT 10;