18 lines
569 B
PL/PgSQL
18 lines
569 B
PL/PgSQL
-- Статистика: топ по количеству сообщений.
|
|
|
|
CREATE OR REPLACE FUNCTION bot.get_top_list()
|
|
RETURNS TABLE(u character varying, n character varying, c bigint)
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT tu.username, du.nick, sum(m.count)::bigint AS cnt
|
|
FROM analytics.messages_daily m
|
|
INNER JOIN bot.tg_users tu ON m.user_id = tu.id_user
|
|
INNER JOIN bot.de_users du ON m.user_id = du.id_user
|
|
GROUP BY tu.username, du.nick
|
|
ORDER BY 3 DESC
|
|
LIMIT 10;
|
|
END;
|
|
$$;
|