Skip to content

Instantly share code, notes, and snippets.

@Zemke

Zemke/dev.sql

Created Nov 14, 2020
Embed
What would you like to do?
set search_path = "db10838396-cwt";
CREATE OR REPLACE FUNCTION timeline(bigint)
RETURNS TABLE
(
timeline text
)
ROWS 1
LANGUAGE SQL
AS
$$
select string_agg(cast(round as text), ',') as timeline
from (select TO_CHAR(t.CREATED, 'yyyy') as year,
'[' || t.id || ',' || TO_CHAR(t.CREATED, 'yyyy') || ',' || coalesce(t.three_way::int, 0) || ',' ||
t.max_rounds || ',' ||
least(greatest((case coalesce(g.g_id, 0) when 0 then 0 else 1 end), max(po.round) + 1),
t.max_rounds - (case when t.three_way then 0 else 1 end)) +
(case
when t.GOLD_WINNER_ID = $1
then 3
when t.SILVER_WINNER_ID = $1
then 2
when t.BRONZE_WINNER_ID = $1
then 1
else 0 end) || ']' as round
from TOURNAMENT t
left join (select g.id as g_id, s.id as s_id, g.TOURNAMENT_ID as t_id, s.USER_ID as u_id
from "group" g
join GROUP_STANDING s on g.ID = s.GROUP_ID) g on g.t_id = t.id and g.u_id = $1
left join (select TOURNAMENT_ID, HOME_USER_ID, AWAY_USER_ID, max(round) as round
from GAME g
join PLAYOFF_GAME po1 on g.PLAYOFF_ID = po1.ID
group by TOURNAMENT_ID, HOME_USER_ID, AWAY_USER_ID) po
on po.TOURNAMENT_ID = t.id and (po.HOME_USER_ID = $1 or po.AWAY_USER_ID = $1)
where t.status = 'FINISHED'
group by year, g.g_id, t.GOLD_WINNER_ID, t.SILVER_WINNER_ID, t.BRONZE_WINNER_ID, t.max_rounds, t.id
order by year asc) as x;
$$;
refresh materialized view user_stats;
update "db10838396-cwt"."user"
set password_hash = null,
password_legacy_hash='51bf293b2a7ebf5d5699e5f54ff9ac7e8ab1cf7b';
update configuration
set value = 'false'
where key = 'EVENT_SOURCE_TWITCH_WEBHOOK';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment