Qfyilyi

Today I want to get a help in creating scores per user in my database. I have this query:

SELECT

    r1.id,

    r1.nickname,

    r1.fecha,

    r1.bestia1,

    r1.bestia2,

    r1.bestia3,

    r1.bestia4

    r1.bestia5

FROM

    reporte AS r1

INNER JOIN

    ( SELECT

          nickname, MAX(fecha) AS max_date

      FROM

          reporte

      GROUP BY

          nickname ) AS latests_reports 

ON latests_reports.nickname = r1.nickname

AND latests_reports.max_date = r1.fecha

ORDER BY

    r1.fecha DESC

that's from a friend from this site who helped me in get "the last record per user in each day", based on this I am looking how to count the results in a ranking daily, weekly or monthly, in order to use statistics charts or google datastudio, I've tried the next:

select id, nickname, sum(bestia1), sum(bestia2), etc...

But its not giving the complete result wich I want. thats why I am looking for help. Additionaly I know datastudio filters where I can show many charts but still I can count completely.

for example, one player in the last 30 days reported 265 monsters killed, but when I use in datastudio my query it counts only the latest value (it can be 12). so I want to count correctly in order to use with charts

SQL records filtered with my query:

One general approach for get the total monsters killed by each user on the latest X days and make a score calculation like the one you propose on the commentaries can be like this:

SET @daysOnHistory = X; -- Where X should be an integer positive number (like 10).



SELECT

    nickname,

    SUM(bestia1) AS total_bestia1_killed,

    SUM(bestia2) AS total_bestia2_killed,

    SUM(bestia3) AS total_bestia3_killed,

    SUM(bestia4) AS total_bestia4_killed,

    SUM(bestia5) AS total_bestia5_killed,

    SUM(bestia1 + bestia2 + bestia3 + bestia4 + bestia5) AS total_monsters_killed,

    SUM(bestia1 +  2 * bestia2 + 3 * bestia3 + 4 * bestia4 + 5 * bestia5) AS total_score

FROM

    reporte

WHERE

    fecha >= DATE_ADD(DATE(NOW()), INTERVAL -@daysOnHistory DAY)

GROUP BY

    nickname

ORDER BY

    total_score DESC

Now, if you want the same calculation but only taking into account the days of the current week (assuming a week starts on Monday), you need to replace the previous WHERE clause by next one:

WHERE

    fecha >= DATE_ADD(DATE(NOW()), INTERVAL -WEEKDAY(NOW()) DAY)

Even more, if you want all the same, but only taking into account the days of the current month, you need to replace the WHERE clause by:

WHERE

    MONTH(fecha) = MONTH(NOW())

For evaluate the statistics on the days of the current year, you need to replace the WHERE clause by:

WHERE

    YEAR(fecha) = YEAR(NOW())

And finally, for evaluation on a specific range of days you can use, for example:

WHERE

    DATE(fecha) BETWEEN CAST("2018-10-15" AS DATE) AND CAST('2018-11-10' AS DATE)

I hope this guide will help you and clarify your outlook.

This will give you number of monster killed in the last 30 days per user :

SELECT

    nickname,

    sum(bestia1) as bestia1,

    sum(bestia2) as bestia2,

    sum(bestia3) as bestia3,

    sum(bestia4) as bestia4,

    sum(bestia5) as bestia5

FROM

    reporte 

WHERE   fecha >= DATE_ADD(curdate(), interval -30 day)

GROUP BY nickName

ORDER BY