Gamestats

People are still making stuff for the Sinclair related machines. Tell us about new games and other software that runs on the Spectrum, ZX80/ZX81, Pentagon and Next.
Post Reply
User avatar
R-Tape
Site Admin
Posts: 6353
Joined: Thu Nov 09, 2017 11:46 am

Gamestats

Post by R-Tape »

In the last 10 years we've had one (fantastic) Strategy Management Game - Telly Heroes.
Posting ^this^ started me thinking about game statistics for the post commercial era. Is anyone with the ability to handle ZXDB* willing to expand on the gamestats I did in the Woot tape magazine? For example:

Image

I think it would be cool to have similar graphs for all manner of thing since 1993:
  • Total games
  • Number of games in each genre
  • Number of games made with AGD, Churrera, 3D game maker (all the game makes listed in ZXDB)
  • Total 128k only
  • ZX81 games
  • 16K games
...and lots more

I made my graph by counting what I could see in the SC archive, but someone with modern coding skills should find this easy to rip from ZXDB (I assume).

I expect this thread to drop like a stone :mrgreen:

*I didn't mean you Einar, I know how busy you are!
Nomad
Manic Miner
Posts: 600
Joined: Thu Dec 28, 2017 12:38 pm

Re: Gamestats

Post by Nomad »

If there is a way to query the database you could do some really nice charts with R.

https://flowingdata.com/2012/12/17/gett ... arts-in-r/

https://www.r-graph-gallery.com/
User avatar
PeterJ
Site Admin
Posts: 6855
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: Gamestats

Post by PeterJ »

Hi, It's on the list of things I posted where we need development help.
User avatar
R-Tape
Site Admin
Posts: 6353
Joined: Thu Nov 09, 2017 11:46 am

Re: Gamestats

Post by R-Tape »

PeterJ wrote: Fri Mar 16, 2018 12:25 pm Hi, It's on the list of things I posted where we need development help.
You mean this thread?

This is different - I'm asking for a volunteer with the relevant skills to refer to ZXDB and make some cool graphs relating to post commercial era software and post them on the forum.

This is not a request for a website feature or more work for the site devs :)
User avatar
PeterJ
Site Admin
Posts: 6855
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: Gamestats

Post by PeterJ »

R-Tape wrote: Fri Mar 16, 2018 12:36 pm
PeterJ wrote: Fri Mar 16, 2018 12:25 pm Hi, It's on the list of things I posted where we need development help.
You mean this thread?

This is different - I'm asking for a volunteer with the relevant skills to refer to ZXDB and make some cool graphs relating to post commercial era software and post them on the forum.

This is not a request for a website feature or more work for the site devs :)
Phew, that's great Dave. We can probably do something in SQL.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: Gamestats

Post by Einar Saukas »

To consider only titles classified as games (excluding game compilations), and also discarding crap games from CSSCGC, you can use the following queries...


Total number of games per genre, for either Spectrum 48K or Spectrum 128K models:

Code: Select all

select count(e.id) as n, g.text as genre from entries e
inner join releases r on r.entry_id = e.id and r.release_seq = 0
inner join genretypes g on g.id = e.genretype_id
inner join machinetypes m on m.id = e.machinetype_id
where g.text like '%Game:%'
and m.text like 'ZX-Spectrum %' and m.text <> 'ZX-Spectrum Next'
and e.is_crap = 0
group by g.text order by g.text;

Total number of games per year, for either Spectrum 48K or Spectrum 128K models:

Code: Select all

select count(e.id) as n, coalesce(r.release_year,'?') as year from entries e
inner join releases r on r.entry_id = e.id and r.release_seq = 0
inner join genretypes g on g.id = e.genretype_id
inner join machinetypes m on m.id = e.machinetype_id
where g.text like '%Game:%'
and m.text like 'ZX-Spectrum %' and m.text <> 'ZX-Spectrum Next'
and e.is_crap = 0
group by r.release_year order by r.release_year;

Total number of games per both year and genre, for either Spectrum 48 or Spectrum 128 models:

Code: Select all

select count(e.id) as n, coalesce(r.release_year,'?') as year, g.text as genre from entries e
inner join releases r on r.entry_id = e.id and r.release_seq = 0
inner join genretypes g on g.id = e.genretype_id
inner join machinetypes m on m.id = e.machinetype_id
where g.text like '%Game:%'
and m.text like 'ZX-Spectrum %' and m.text <> 'ZX-Spectrum Next'
and e.is_crap = 0
group by r.release_year,g.text order by r.release_year,g.text;

If you want these results for games were minimal requirement is Spectrum 128, replace the corresponding condition to this:

Code: Select all

and m.text like 'ZX-Spectrum 128%'

Or to obtain results for ZX81 games:

Code: Select all

and m.text like 'ZX81%'

If you want to exclude "mods" from any of the queries above, then add the following condition:

Code: Select all

and e.is_mod = 0

Finally, if you want to restrict results to games produced using a Game Creator, then also add the following condition to any of the queries above:

Code: Select all

and e.id in (select entry_id from frameworks f inner join entries u on f.util_id = u.id where u.genretype_id = 52)

It only took me a few minutes to write these queries, but it would take me considerably more time to select, organize and upload each result somewhere. Therefore I will leave this task as exercise for the reader! :)
User avatar
PeterJ
Site Admin
Posts: 6855
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: Gamestats

Post by PeterJ »

Thanks [mention]Einar Saukas[/mention] I use Microsoft Power BI at work which can read direct from MySQL. When I have time I can produce a graphical dashboard with the data.
User avatar
druellan
Dynamite Dan
Posts: 1466
Joined: Tue Apr 03, 2018 7:19 pm

Re: Gamestats

Post by druellan »

R-Tape wrote: Mon May 21, 2018 1:28 pm (there is such a report in the 2017 Woot).
Didn't noticed this!
Ok, this is cool BUT I think such a report should be available on a non-spectrum format. Does not matter if the style is retro or not, but if you bundle this inside a tape magazine, can be difficult to share.
I love the idea of the end-of-the-year tape magazine, but I think this information worth a better format, more easy read and share.
User avatar
R-Tape
Site Admin
Posts: 6353
Joined: Thu Nov 09, 2017 11:46 am

Re: Gamestats

Post by R-Tape »

druellan wrote: Mon May 21, 2018 1:56 pm Ok, this is cool BUT I think such a report should be available on a non-spectrum format.
I agree. It goes in the magazine because I am looking for interesting contributions but I can provide it in a modern format as well.

The chart at the top of this thread would need correcting anyway, it is missing quite a few BASIC games (now in ZXDB).

It would need a lot more analysis but it looks like the overall output has not fallen, we still get the AAA games* like Sword of IANNA, Ninja Gaiden, Mighty Final Fight, Egghead6 but it seems designer games have replaced the beginner's coded games.

So we see less games like the Burgle series (quirky, jerky, UDG) and more platform/arcade games that are beyond a beginner coder's wildest dreams.

I could be talking total rubbish, which is why it'd be cool for someone to look at homebrew in more detail.

*possibly more now because of ZX-Dev
Post Reply