Database

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