-- Case 1:
-- Schema:
CREATE TABLE IF NOT EXISTS StickerLogs (
  userid BIGINT NOT NULL,
  chatid BIGINT NOT NULL,
  msgid BIGINT NOT NULL,
  fileid TEXT NOT NULL,
  packname TEXT,
  emoji TEXT,
  instant BIGINT NOT NULL,
  hash BYTEA NOT NULL
);

-- Query:

SELECT fileid, COUNT(*) AS uses
  FROM StickerLogs
 WHERE chatid = $1 AND instant > $2
 GROUP BY hash
 ORDER BY uses DESC
 LIMIT 25

-- Case 2:
-- Schema

CREATE TABLE IF NOT EXISTS MessageLogs (
  chatid BIGINT NOT NULL,
  userid BIGINT NOT NULL,
  msgid BIGINT NOT NULL,
  message TEXT NOT NULL,
  instant BIGINT NOT NULL
);

CREATE TABLE IF NOT EXISTS EditLogs (
  chatid BIGINT NOT NULL,
  userid BIGINT NOT NULL,
  msgid BIGINT NOT NULL
);

-- Query

SELECT MessageLogs.userid,
       edits.uniqueEdits * 1.0 / COUNT(*) * 100.0 AS percentage,
       edits.totalEdits AS totalEdits
  FROM MessageLogs
         JOIN (SELECT COUNT(DISTINCT EditLogs.msgid) AS uniqueEdits,
                      COUNT(EditLogs.msgid) AS totalEdits,
                      EditLogs.userid
                 FROM EditLogs
                WHERE EditLogs.chatid = $1
                GROUP BY EditLogs.userid
         ) AS edits ON MessageLogs.userid = edits.userid
 WHERE MessageLogs.chatid = $1
 GROUP BY MessageLogs.userid
 ORDER BY percentage DESC