vVKn6V9M

1-- Case 1:
2-- Schema:
3CREATE TABLE IF NOT EXISTS StickerLogs (
4  userid BIGINT NOT NULL,
5  chatid BIGINT NOT NULL,
6  msgid BIGINT NOT NULL,
7  fileid TEXT NOT NULL,
8  packname TEXT,
9  emoji TEXT,
10  instant BIGINT NOT NULL,
11  hash BYTEA NOT NULL
12);
13
14-- Query:
15
16SELECT fileid, COUNT(*) AS uses
17  FROM StickerLogs
18 WHERE chatid = $1 AND instant > $2
19 GROUP BY hash
20 ORDER BY uses DESC
21 LIMIT 25
22 
23-- Case 2:
24-- Schema
25
26CREATE TABLE IF NOT EXISTS MessageLogs (
27  chatid BIGINT NOT NULL,
28  userid BIGINT NOT NULL,
29  msgid BIGINT NOT NULL,
30  message TEXT NOT NULL,
31  instant BIGINT NOT NULL
32);
33
34CREATE TABLE IF NOT EXISTS EditLogs (
35  chatid BIGINT NOT NULL,
36  userid BIGINT NOT NULL,
37  msgid BIGINT NOT NULL
38);
39
40-- Query
41
42SELECT MessageLogs.userid,
43       edits.uniqueEdits * 1.0 / COUNT(*) * 100.0 AS percentage,
44       edits.totalEdits AS totalEdits
45  FROM MessageLogs
46         JOIN (SELECT COUNT(DISTINCT EditLogs.msgid) AS uniqueEdits,
47                      COUNT(EditLogs.msgid) AS totalEdits,
48                      EditLogs.userid
49                 FROM EditLogs
50                WHERE EditLogs.chatid = $1
51                GROUP BY EditLogs.userid
52         ) AS edits ON MessageLogs.userid = edits.userid
53 WHERE MessageLogs.chatid = $1
54 GROUP BY MessageLogs.userid
55 ORDER BY percentage DESC