How to get the number of users with less time in each Client?

Question

I have 2 tables:

CREATE TABLE `deathrun_records` (
    `sid64` STRING,
    `mapname`   STRING,
    `seconds`   REAL
);

CREATE TABLE `clan_members` (
    `sid64` STRING,
    `nome_clan` STRING,
    `prop`  STRING
);

The first table stores users' time on each map, and the second saves the users' club.

I need a SELECT that returns the clans with users who have more records (less time on a map).

What I’ve done so far:

SELECT COUNT(*) AS recordes FROM deathrun_records d JOIN clan_members c ON c.sid64 = d.sid64 GROUP BY c.nome_clan ORDER BY recordes

My problem is to create a WHERE CLAUSE to catch only the player with less time on each map. How to do?

Answer 1

First you need to do a query grouping the shortest time per Clan, and then a query on the outside to catch who has the shortest time from this Clan, then the query would look like this:

SELECT
    d2.*,
    c2.*
FROM deathrun_records d2
INNER JOIN clan_members c2
ON c2.sid64 = d2.sid64
INNER JOIN(
SELECT 
    d.mapname,
    d.sid64,
    d.seconds,
    c.nome_clan,
    MIN(d.seconds) seconds_join
FROM deathrun_records d 
JOIN clan_members c 
ON c.sid64 = d.sid64 
GROUP BY c.nome_clan
ORDER BY nome_clan, seconds DESC) as aux
ON (aux.nome_clan = c2.nome_clan 
    AND seconds_join = d2.seconds 
)

So you can count the result of query grouping by d2.seconds and c2.nome_clan then you will have how many players per Clan have the shortest time, this way we are returning users with shortest time per Clan time, if you want to see by global time just remove from GROUP BY of the first consultation the field nome_clan.

In the query I made above you will have the players with less CLAN time in that I have now you Qtd players by Clan have the lowest GLOBAL time, follows:

SELECT
    COUNT(*) AS qtd_player,
    c2.nome_clan
FROM deathrun_records d2
INNER JOIN clan_members c2
ON c2.sid64 = d2.sid64
INNER JOIN(
SELECT 
    d.mapname,
    d.sid64,
    d.seconds,
    c.nome_clan,
    MIN(d.seconds) seconds_join
FROM deathrun_records d 
JOIN clan_members c 
ON c.sid64 = d.sid64 
ORDER BY nome_clan, seconds DESC) as aux
ON seconds_join = d2.seconds 
GROUP BY nome_clan