# What would be best data model for finding difference within n, n-1 and n-2 elements?

This is from betting domain which has something that is called a long list: a list of a "home team win/draw/away team win" markets for 13 games. A punter can select any combination of the possible outcomes which are encoded in a following way:

``````1 - home team wins
2 - draw
4 - away team wins
3 - home team wins or draw
5 - home team wins or away team wins
6 - draw or away team wins
7 - home team wins or draw or away team wins
``````

Meaning a `[3, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7]` represents a selection where punter put following bets:

• "home team wins or draw" in a first game
• "home team wins" in games 2-12
• "home team wins or draw or away team wins" in 13th game

After games are finished there will be another 13 elements list representing winning outcomes, for example: `[4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]` means that in a first game away team won and in all other games home team won.

Let's take this bet `[3, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7]` and this query as an example `[4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]`.

Next we have a following correspondence between selected outcomes and actual results

``````1 - 1, 3, 5, 7
2 - 2, 3, 6, 7
4 - 4, 5, 6, 7
``````

Meaning for an actual result of 13 games `[4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]` following bets considered to be a winning bets:

``````[4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
[4, 3, 1, 5, 1, 7, 1, 1, 1, 1, 1, 1, 1]
[5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
[6, 1, 7, 1, 1, 5, 1, 1, 3, 3, 3, 3, 1]
[7, 1, 5, 5, 5, 1, 1, 1, 1, 7, 7, 7, 1]
``````

The question is what is the best way to model this in database for following use cases:

• find bets where all 13 game results were guessed correctly
• find bets where 12 game results were guessed correctly
• find bets where 11 game results were guessed correctly

Examples of queries for following use-cases will be highly appreciated ;))

Possible realization:

``````id SERIAL PRIMARY KEY,
punter_id INT,
bet_number INT,
guess_number TINYINT, -- from 1 to 13
guess TINYINT, -- from 0 (or 1) to 7
UNIQUE (punter_id, bet_number, guess_number)
``````

Each bet consists from 13 records for each punter.

The result is placed into the table with similar structure:

``````id SERIAL PRIMARY KEY,
bet_number INT,
result_number TINYINT, -- from 1 to 13
result TINYINT, -- from 0 (or 1) to 7
UNIQUE (bet_number, result_number)
``````

So you join these tables by `(bet_number, guess_number)`=`(bet_number, result_number)`, group by `punter_id` (or by `(punter, bet_number, result_number)` when you'll obtain the result for a lot of bets in one query), and calculate `SUM( CASE guess & result WHEN 0 THEN 0 ELSE 1 END )`. This sum is the amount of positive guesses per bet.

• Hi @Akina. Thanks for your tip - I didn't use case a lot and put together this gist.github.com/lessless/63bce14722155199fcc693791c5d9248 sandbox to familiarize with the concept. Can you please tell how close is it to idea that you described? Commented Dec 26, 2019 at 21:19
• @lessless how close is it to idea that you described? Far enough in structure, while compare this two approaches the one by the link is de-normalized. And the same in the algo of result calculation. And the link identifies the bet by the week (assuming one bet per week) whereas in my opinion it has synthetic number only (and must be described in separate table where all its attributes, including week, are stored). Commented Dec 27, 2019 at 5:11