Hi,
i'm in need for some SQL queries.
And possibly some database optimizations.
Here is a small part of the database (tables and some insertions): https://www.db-fiddle.com/f/9knJYioBumiy932cBVBCMv/0
and a quick explanation of the tables:
results: identifies the results for a specific tid (tournament)
matches: gives the result for each tid of the matches disputed. the result
(1) indicates if the winner was team1+deckone or (2), team2,decktwo.
The queries i will need are:
a) Select distinct from results the deck and number of times it appears
example expected:
Deck : total
------------
red : 5
Yellow : 2
Blue: 6
b) A select from the table matches that will give the win rates from a specified deck.
meaning, counting all the ocorrences of deck, and times it won, against what. Ignore when deckone = decktwo
example expected:
Deck : wins : loses : total
____________________________
Yellow : 5 : 2 : 7
Blue : 2 : 2 : 4
c) Same as previous querie, except it relates to other decks.
global win = total wins / total plays.
win vs x = wins vs deck x / plays vs deck x
example expected (don't need the % calculated, i can do that, just need to wins and totals).
Deck : global win : win vs Yell : win vs blue : win vs Pink
-------------------------------------------------------------
Yell : 50% : - : 20% : 50% : 80%
Blue : 40% : 30% : - : 20% : 45%
Pink : 90% : 50% : 100% : 70% : -
d) A mix of a) + c) Meaning to include a new collumn with: (number of deck)/total.
Best regards