Enhance kotter report to include "Besties" to help track the best person to reach out to the kotter #13
Description
Below is the Besties report showing just 2024 data - could be adjusted for the add to kotter
SELECT u.user_name AS PAX, u2.user_name AS Buddy, c.bd_count
FROM
(SELECT *
, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY bd_count DESC) AS buddy_rank
FROM
(SELECT user_id, buddy_user_id, COUNT() AS bd_count
FROM
(
SELECT ba., ba2.user_id AS buddy_user_id
FROM bd_attendance ba
INNER JOIN
bd_attendance ba2
ON ba.ao_id = ba2.ao_id
AND ba.date = ba2.date
AND YEAR(ba.date) = 2024
AND ba.q_user_id = ba2.q_user_id
AND ba.user_id <> ba2.user_id
) a GROUP BY 1, 2
) b) c
INNER JOIN users u
ON c.user_id = u.user_id
INNER JOIN users u2
ON c.buddy_user_id = u2.user_id
WHERE c.buddy_rank = 1
ORDER BY c.bd_count DESC
;