Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
scorebot_production=# select teams.name as team, t1.id as token, s.name as svc,
count(*) as submitters, (19 / count(*)) as kept, (19 / (count(*) - 1) - 19 / count(*)) as deprived
from redemptions r1, tokens t1, instances i1, teams, services s
where r1.token_id in (select r.token_id as token from redemptions r, instances i, tokens t
where r.token_id = t.id and t.instance_id = i.id and i.team_id = r.team_id
group by token) and r1.token_id = t1.id and t1.instance_id = i1.id
and i1.team_id = teams.id and i1.service_id = s.id
group by teams.name, t1.id, s.name having count(*) > 1 order by teams.name asc, t1.id asc;
team | token | svc | submitters | kept | deprived
-------------------+-------+----------+------------+------+----------
Alternatives | 7951 | atmail | 12 | 1 | 0
Alternatives | 20951 | trouver | 2 | 9 | 10
APT8 | 13829 | bookworm | 10 | 1 | 1
APT8 | 14081 | bookworm | 9 | 2 | 0
APT8 | 14207 | bookworm | 8 | 2 | 0
APT8 | 14585 | bookworm | 6 | 3 | 0
APT8 | 14711 | bookworm | 9 | 2 | 0
APT8 | 15089 | bookworm | 9 | 2 | 0
APT8 | 21389 | bookworm | 9 | 2 | 0
APT8 | 21515 | bookworm | 11 | 1 | 0
APT8 | 21641 | bookworm | 10 | 1 | 1
APT8 | 23152 | atmail | 15 | 1 | 0
APT8 | 24286 | atmail | 17 | 1 | 0
APT8 | 24412 | atmail | 18 | 1 | 0
APT8 | 25420 | atmail | 16 | 1 | 0
blue lotus | 5315 | bookworm | 7 | 2 | 1
blue lotus | 5651 | bookworm | 9 | 2 | 0
blue lotus | 5735 | bookworm | 9 | 2 | 0
blue lotus | 7414 | atmail | 11 | 1 | 0
blue lotus | 7750 | atmail | 10 | 1 | 1
blue lotus | 7834 | atmail | 10 | 1 | 1
blue lotus | 7918 | atmail | 11 | 1 | 0
blue lotus | 8002 | atmail | 11 | 1 | 0
blue lotus | 8086 | atmail | 11 | 1 | 0
blue lotus | 8170 | atmail | 11 | 1 | 0
blue lotus | 8254 | atmail | 13 | 1 | 0
blue lotus | 8380 | atmail | 12 | 1 | 0
blue lotus | 10396 | atmail | 8 | 2 | 0
blue lotus | 10522 | atmail | 8 | 2 | 0
blue lotus | 10648 | atmail | 8 | 2 | 0
blue lotus | 10774 | atmail | 6 | 3 | 0
blue lotus | 10900 | atmail | 8 | 2 | 0
blue lotus | 11026 | atmail | 8 | 2 | 0
blue lotus | 11152 | atmail | 8 | 2 | 0
blue lotus | 11278 | atmail | 8 | 2 | 0
blue lotus | 11404 | atmail | 8 | 2 | 0
blue lotus | 11530 | atmail | 8 | 2 | 0
blue lotus | 11656 | atmail | 7 | 2 | 1
blue lotus | 11908 | atmail | 8 | 2 | 0
blue lotus | 12034 | atmail | 8 | 2 | 0
blue lotus | 12160 | atmail | 8 | 2 | 0
blue lotus | 12286 | atmail | 8 | 2 | 0
blue lotus | 12412 | atmail | 8 | 2 | 0
blue lotus | 12538 | atmail | 8 | 2 | 0
blue lotus | 12664 | atmail | 8 | 2 | 0
blue lotus | 12790 | atmail | 9 | 2 | 0
blue lotus | 12916 | atmail | 9 | 2 | 0
blue lotus | 13042 | atmail | 9 | 2 | 0
blue lotus | 13168 | atmail | 11 | 1 | 0
blue lotus | 13294 | atmail | 10 | 1 | 1
blue lotus | 13420 | atmail | 10 | 1 | 1
blue lotus | 13546 | atmail | 11 | 1 | 0
blue lotus | 13672 | atmail | 13 | 1 | 0
blue lotus | 13798 | atmail | 8 | 2 | 0
blue lotus | 20932 | avoir | 3 | 6 | 3
blue lotus | 21184 | avoir | 4 | 4 | 2
clgt | 6848 | bookworm | 10 | 1 | 1
clgt | 10040 | bookworm | 12 | 1 | 0
clgt | 21883 | atmail | 7 | 2 | 1
clgt | 22009 | atmail | 14 | 1 | 0
clgt | 22261 | atmail | 13 | 1 | 0
men in black hats | 21126 | lonetuna | 2 | 9 | 10
men in black hats | 21252 | lonetuna | 2 | 9 | 10
men in black hats | 21378 | lonetuna | 2 | 9 | 10
men in black hats | 21504 | lonetuna | 2 | 9 | 10
men in black hats | 21630 | lonetuna | 2 | 9 | 10
men in black hats | 21882 | lonetuna | 2 | 9 | 10
men in black hats | 22008 | lonetuna | 2 | 9 | 10
men in black hats | 22134 | lonetuna | 2 | 9 | 10
men in black hats | 23177 | reeses | 2 | 9 | 10
men in black hats | 23303 | reeses | 2 | 9 | 10
men in black hats | 25067 | reeses | 2 | 9 | 10
men in black hats | 25319 | reeses | 3 | 6 | 3
men in black hats | 25445 | reeses | 3 | 6 | 3
men in black hats | 25571 | reeses | 2 | 9 | 10
men in black hats | 25697 | reeses | 2 | 9 | 10
men in black hats | 25823 | reeses | 2 | 9 | 10
men in black hats | 25949 | reeses | 2 | 9 | 10
men in black hats | 26075 | reeses | 2 | 9 | 10
men in black hats | 26201 | reeses | 2 | 9 | 10
men in black hats | 26327 | reeses | 2 | 9 | 10
men in black hats | 26453 | reeses | 2 | 9 | 10
men in black hats | 26579 | reeses | 2 | 9 | 10
men in black hats | 26705 | reeses | 2 | 9 | 10
pwnies | 7321 | atmail | 5 | 3 | 1
pwnies | 7825 | atmail | 12 | 1 | 0
pwnies | 8749 | atmail | 14 | 1 | 0
pwnies | 9505 | atmail | 17 | 1 | 0
pwnies | 9631 | atmail | 17 | 1 | 0
pwnies | 9757 | atmail | 17 | 1 | 0
pwningyeti | 5846 | bookworm | 10 | 1 | 1
raon_ASRT | 3980 | bookworm | 2 | 9 | 10
raon_ASRT | 4043 | bookworm | 2 | 9 | 10
raon_ASRT | 6898 | atmail | 2 | 9 | 10
raon_ASRT | 6982 | atmail | 2 | 9 | 10
raon_ASRT | 7234 | atmail | 6 | 3 | 0
raon_ASRT | 7318 | atmail | 6 | 3 | 0
raon_ASRT | 7402 | atmail | 10 | 1 | 1
raon_ASRT | 7486 | atmail | 8 | 2 | 0
raon_ASRT | 7570 | atmail | 7 | 2 | 1
raon_ASRT | 7738 | atmail | 4 | 4 | 2
raon_ASRT | 7906 | atmail | 2 | 9 | 10
raon_ASRT | 7990 | atmail | 11 | 1 | 0
raon_ASRT | 8074 | atmail | 12 | 1 | 0
raon_ASRT | 8158 | atmail | 8 | 2 | 0
raon_ASRT | 8620 | atmail | 13 | 1 | 0
raon_ASRT | 8746 | atmail | 13 | 1 | 0
raon_ASRT | 8872 | atmail | 13 | 1 | 0
raon_ASRT | 8998 | atmail | 11 | 1 | 0
raon_ASRT | 9376 | atmail | 16 | 1 | 0
raon_ASRT | 9502 | atmail | 15 | 1 | 0
raon_ASRT | 9628 | atmail | 16 | 1 | 0
raon_ASRT | 9754 | atmail | 14 | 1 | 0
raon_ASRT | 21428 | avoir | 2 | 9 | 10
shell corp | 7420 | atmail | 11 | 1 | 0
shell corp | 7504 | atmail | 9 | 2 | 0
shell corp | 23632 | atmail | 10 | 1 | 1
shell corp | 23634 | lonetuna | 2 | 9 | 10
shell corp | 23708 | avoir | 7 | 2 | 1
shell corp | 24086 | avoir | 7 | 2 | 1
shell corp | 24136 | atmail | 13 | 1 | 0
shell corp | 24138 | lonetuna | 2 | 9 | 10
shell corp | 24181 | reeses | 3 | 6 | 3
shell corp | 24338 | avoir | 7 | 2 | 1
shell corp | 24464 | avoir | 5 | 3 | 1
shellphish | 4883 | bookworm | 6 | 3 | 0
shellphish | 4946 | bookworm | 2 | 9 | 10
WOWHacker-BI0S | 9022 | atmail | 14 | 1 | 0
WOWHacker-BI0S | 9148 | atmail | 16 | 1 | 0
WOWHacker-BI0S | 9274 | atmail | 14 | 1 | 0
WOWHacker-BI0S | 9400 | atmail | 15 | 1 | 0
WOWHacker-BI0S | 9526 | atmail | 17 | 1 | 0
WOWHacker-BI0S | 9652 | atmail | 18 | 1 | 0
WOWHacker-BI0S | 9778 | atmail | 18 | 1 | 0
WOWHacker-BI0S | 9904 | atmail | 18 | 1 | 0
WOWHacker-BI0S | 10030 | atmail | 18 | 1 | 0
WOWHacker-BI0S | 10156 | atmail | 18 | 1 | 0
WOWHacker-BI0S | 10282 | atmail | 17 | 1 | 0
WOWHacker-BI0S | 10408 | atmail | 19 | 1 | 0
(139 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment