Looks straight out of 2010, but the details and the data are phenominal. Great job on this!

You may be intriuged to port into DigitalOcean and their App platform. The CI/CD as well as horizontal/vertical scalability is amazing for these types of projects - you just won't be able to run a local SQL DB in this environment, but you could just run a Droplet for an additional $5 or an AWS instance for free (for up to 12 months) or GCP (for up to 12 months) I have some credit I can send your way if you wish to experiment with this.

Looking forward to how this project grows!

this is sick as fuck

Thanks! Any web design help would be great, since it's not really my area of expertise.

I looked into it a while back, but I had $100 of credit on Azure, so I decided to use them (at least initially).

Gotcha, there's so much credit now everywhere lol.

To answer the web design help, try to use programs like AdobeXD or Figma. Both are free too!

These stats are really good. What I'd love to see now is if you could make a rankings leaderboard out of all the data you've made.

Yeah, I'd like to add some kind of elo eventually. But there are a lot of other things which need doing first.

would be sick if you could filter certain logs. shit like filtering out ultiduo logs

Good idea. I made an issue for it.

edit: Seemed really simple, so I just added it. Have fun ;)

sick as frick, really great project.

legend

this is dope, thanks for putting the time in (:

A little update:

I'm migrating the database from SQLite to PostgreSQL. I started this last week, and did the switchover this past weekend. However, some of the queries are now taking way longer than they used to. I am currently working on this. Availability might be a bit spotty until I can get to the bottom of things. I am also trying to get log importing back up, but that is going much slower than before too. Please be patient for the next few days :)

In the longer term, performance was and is a big problem with the site, even before switching databases. I would love if some more experienced developers could look over my schema/queries and help out.

I'll take a look if you share some slow queries (use postgres' slow query logger) and the entire DDL (pg_dump --schema-only) and put it in a github gist.

Ok, I've addressed most of the major performance issues. The responsiveness should be back to what it was before the migration. I'm still importing logs from the past week, but it is going much faster than it had been.

Here's the explain analyze output for the peers query, which is currently the slowest page on the site. And here is the schema. My current plan is to move most columns out of the round table to reduce the amount of data we have to read in that inner loop to determine win/loss statistics.

Hehe, those are some cool queries, I was hoping you were just missing some indexes or something, but it looks like you got all that covered.

An on-the-fly aggregation like the peers is always going to be challenging, especially since you're trying to find the top 100 players most played with/against.
One thing I would consider, is to move "log_wlt" and "player_last" from views to materialized views (unlike a view these need their own indexes). You could update these materialized views (concurrently) after a new batch of log files is imported. This might improve part of the peers query.

does this site has any sort of API? it would be cool to have it because then we could use it for skill prediction on tf2pickup.org I guess + some option to filter 6v6/hl/2v2 games out from the rest when checking a player
that would be way cooler than looking at the divisions on etf2l for europeans, in case of burgers there is probably no good way to do that atm
no idea about apac

ps: this is probably the biggest issue why we don't have a european pickup site atm

couple things i noticed

-tried filtering blaze's logs down to just soldier on product in the logs.tf era, and it returned nothing
-is there a way to generate winrate after filtering out ultiduo and stuff?
-b4nny has 283% acc on pyro
-class stats seem inaccurate. it is claiming arrek is undefeated on sniper, n=5. is that like, logs where he played majority sniper

cheers. don't change the design. every website built after 2010 looks like shit

the pyro acc is possible because of the detonator or other flare guns

Hm, that's a good idea. Previously, player_last was a table, which I suppose is a kind of manually-materialized view.

Nope. Please send a PR :)

This is being tracked by this issue. But it's not very high on the list of priorities atm. Though part of the reason for switching to postgres in the first place was so I could have multiple processes modifying the database at once, such as a process which calculated elo.

I want to start scraping RGL at some point (see this issue). Unfortunately, RGL doesn't really have an API, so I'd have to scrape their site :l. Honestly, if ETF2L has an API I would look into them first.

Can you link? Thanks.

Nope, sorry. I'm working on adding filters for every page on the site, but the overview hasn't gotten that treatment yet. Though perhaps I could just add this to the totals page...

Basically this. At the moment there is not too much processing on top of raw stats from logs.tf. So if/when I write my own logs parser we can address this. But until then pyro will always have like 3x the hits vs shots.

Winrate is calculated based on games with at least ⅔ time on this class. You can hover over the column headers and the tooltip will show some of the criteria. DPM, accuracy, and time played are based on all times you played that class.

I didn't have much of a choice :)

(that being said, if anyone can help me make a logo/favicon or can help with the CSS, I'd appreciate it)

https://api.etf2l.org/

Feature update:

• You can now sort the logs, peers, and weapons pages by (almost) every column. So e.g. you can find your highest DPM log, or the player you have the best winrate with.
  
• There is now a leaderboard which ranks players by winrate with 50 wins/losses added (to help filter out players who have 100% winrate over 5 games). You can also sort by raw winrate, time played, and number of logs played. In addition, you can filter by class, map, and format, making it easy to answer questions like "Who's the best pyro player in Prolander on koth maps". Currently, leaderboard stats are recalculated every night.
  
• And just for glass, I added the overview stats (winrate, W-L-T, etc.) to the totals page so you can filter by class.

Feature update:

• Avatars are now displayed on player profiles, search results, and on the leaderboard and peers pages. Player pages now use their name on steam.
  
• I added a temporary site logo/favicon. I am still looking for someone to design a proper one.
  
• The totals page now shows averages either per minute (for damage) or per 30m (for kills, etc.).
  
• The weapons page now shows totals for kills, damage, logs, and time played.
  
• There is now a discord which you can use to suggest features or report bugs.

Feature update:

• You can now view logs on the site. It is possible to combine up to five logs and view them just like an individual log. This should hopefully make manual log combiners mostly unnecessary.
  
  Some stats are not displayed. Medic stats like "average time before healing" are not displayed because it is impossible to display them accurately for multiple logs. In the future, I may display them as a list. Other stats like killstreaks and per-round stats are not currently extracted from the log summary information.
  
• Replaced the trends chart library with C3.js (thanks Bv!). This should help with performance.
  
• You can now vary the size of the rolling average on the trends page.
  
• There are now links to RGL, UGC, ETF2L, and OZFortress on player profile pages.

is the "peers" page using all the logs? It seems to be missing peers I played with in the distant past a lot

EDIT okay I see now the oldest log is 2019

Fwiw you can get this by multiplying avg time before healing by deaths for each log, to get total time, then add this up and divide by total deaths.

I've been holding off on importing all the logs

• so I can work out the kinks in the importer
  
• so I can work on the performance (more data means slower site)
  
• until I migrate to a server with a larger disk

This will give incorrect results. You need to use respawns and not deaths, since you always respawn at the start of each round. Unfortunately, this is not recorded by logs.tf. Really, the only way to fix this would be to use an alternative parser.

https://cdn.discordapp.com/attachments/556302040943296512/900634192583467038/s7hardlyworking.png

Just had a look at a log file (link, vod) and it turns out neither of us are right - it almost measures the time between spawning and healing damage (ie not just buffing someone on rollout) but that doesn't seem to be what it's actually doing. Looking for

"4.T Domo<3><[U:1:96525192]><Blue>" triggered "first_heal_after_spawn"

and working backwards, we see that on the first mid, it took Domo 20.1s to trigger this (healing toemas). Before that happened, he buffed players from full health to rollout, healed papi from his pocket rollout, then at mid papi took damage and was healed (though not enough damage to remove his buff). Finally, toemas took damage from the other team, taking him below full health, and was healed by Domo to trigger the log event.

I've not checked in this detail on other logs, but this suggests "Average time before healing" is time before healing damage done by the other team to take a player below full health, and as such is not only difficult to reverse engineer but also mildly pointless.

Hey there!
Guy that spent way to much time trying to recreate the logs.tf parser as closely as possible here:

"Average time before healing" most definitely corresponds to the event "first_heal_after_spawn".
All it does is just save all the times logged by that in a list and average it out at the end.

The reason it takes 20.1s before the event gets logged is because of the decision made by F2 (who wrote the medicstats SM plugin) which is to wait at least 20 seconds from when the last round ended before saying that the medic "spawned".
You can see the relevant lines related to logging the event "first_heal_after_spawn" here and the code where it sets the "MedicInitialHealSpawnTime" here.
Hope that clears up all the confusion around it :)