From 79b3e8b0be084a22d4f30c5933935efe6ec1f743 Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Thu, 4 May 2017 22:32:05 +0200
Subject: fame crunching

---
 crunch_team.sql | 34 ++++++++++++++++++++++++++++++++++
 1 file changed, 34 insertions(+)
 create mode 100644 crunch_team.sql

(limited to 'crunch_team.sql')

diff --git a/crunch_team.sql b/crunch_team.sql
new file mode 100644
index 0000000..4133cf4
--- /dev/null
+++ b/crunch_team.sql
@@ -0,0 +1,34 @@
+UPDATE team_membership tm
+JOIN (
+    SELECT
+    tm.id AS tm_id,
+    SUM(
+        (1 + (tm_cnt-2) * 0.3) *
+        (p.winner * 0.3 + 0.7) *
+        (CASE
+            WHEN tm.status='initiate' THEN 10
+            WHEN tm.status='member' THEN 75
+            WHEN tm.status='veteran' THEN 100
+            WHEN tm.status='officer' THEN 125
+            WHEN tm.status='leader' THEN 125
+        END)
+    ) AS fame
+    FROM (
+        SELECT
+        t.id AS t_id,
+        m.api_id AS m_api_id,
+        COUNT(tm.id) AS tm_cnt
+        FROM participant p
+        JOIN player pl ON p.player_api_id = pl.api_id
+        JOIN team_membership tm ON pl.api_id = tm.player_api_id
+        JOIN team t ON tm.team_id = t.id
+        JOIN roster r ON p.roster_api_id = r.api_id
+        JOIN `match` m ON r.match_api_id = m.api_id
+        WHERE t.id IN (:team_ids)
+        GROUP BY t.id, m.api_id, r.id
+    ) AS cnt_by_m
+    JOIN participant p ON p.match_api_id = cnt_by_m.m_api_id
+    JOIN team_membership tm ON cnt_by_m.t_id = tm.team_id AND tm.player_api_id = p.player_api_id
+    GROUP BY tm.id
+) AS fame_diff ON tm.id = fame_diff.tm_id
+SET tm.fame = fame_diff.fame
-- 
cgit v1.3.1