search

Blackmorse / hat-all-stats

new alltid
11 stars 4 forks source link

Add to overview pages number of new teams #382

Closed Blackmorse closed 2 years ago

Blackmorse commented 2 years ago

Get team's creation date from team_details table

Blackmorse commented 2 years ago

Would be nice to have a chart over the time. Calculate via request or add flag "is_new_user" at akka-loader?

Blackmorse commented 2 years ago

Request for showing days team exists:

SELECT
    season,
    round,
    ((toMonday(today()) - 1) - (((79 /* current season/* - season) * 16) * 7)) - (abs(12 /* current round */ - round) * 7) AS that_day,
    that_day - founded_date AS diff
FROM hattrick.team_details
WHERE team_id = 615797
ORDER BY
    season ASC,
    round ASC
Blackmorse commented 2 years ago

the same one for the history Note: 12 = currentRound 79 = currentSeason

SELECT
    season,
    round,
    count()
FROM
(
    SELECT
        team_name,
        season,
        round,
        founded_date,
        league_id,
        ((toMonday(today())) - (((79 - season) * 16) * 7)) - ((12 - round) * 7) AS that_day,
        that_day - founded_date AS diff
    FROM hattrick.team_details
    WHERE diff <= multiIf(round = 1, 21, 7)
    ORDER BY
        season ASC,
        round ASC
)
GROUP BY
    season,
    round
ORDER BY
    season ASC WITH FILL TO 79 + 1,
    round ASC WITH FILL TO 12 + 1