CREATE TABLE user (
username TEXT PRIMARY KEY,
display_name TEXT,
created_date TEXT,
about_me TEXT,
status_msg TEXT
);
CREATE TABLE friendship (
user_1 INTEGER,
user_2 INTEGER,
friends_since TEXT,
PRIMARY KEY (user_1,user_2),
FOREIGN KEY (user_1) REFERENCES user(user_id),
FOREIGN KEY (user_2) REFERENCES user(user_id)
);
CREATE TABLE server (
server_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
about TEXT,
created_date TEXT
);
CREATE TABLE membership (
user_id INTEGER,
server_id INTEGER,
member_since TEXT,
PRIMARY KEY (user_id,server_id),
FOREIGN KEY (user_id) REFERENCES user(user_id),
FOREIGN KEY (server_id) REFERENCES server(server_id)
);
-- Bit position for each permission for use in the perms bitmask in 'server_role'
CREATE TABLE perm_bitmask (
perm_num INTEGER PRIMARY KEY AUTOINCREMENT CHECK(perm_num <= 32),
description TEXT
);
CREATE TABLE server_role (
user_id INTEGER,
server_id INTEGER,
role_name TEXT,
perm_bitmask INTEGER, -- Combination of perms from the 'perm_bitmask' table.
-- OR multiple roles together to get the total perms for a user
FOREIGN KEY (user_id) REFERENCES user(user_id),
FOREIGN KEY (server_id) REFERENCES server(server_id)
);
CREATE TABLE channel (
channel_id INTEGER PRIMARY KEY AUTOINCREMENT,
server_id INTEGER,
name TEXT,
is_voice BOOLEAN,
FOREIGN KEY (server_id) REFERENCES server(server_id)
);
CREATE TABLE message (
message_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
channel_id INTEGER,
content TEXT,
timestamp TEXT,
has_embed BOOLEAN,
FOREIGN KEY (user_id) REFERENCES user(user_id),
FOREIGN KEY (channel_id) REFERENCES channel(channel_id)
);
CREATE TABLE voice_activity (
user_id INTEGER,
channel_id INTEGER,
type TEXT CHECK(type IN ('connect', 'disconnect')),
timestamp TEXT,
FOREIGN KEY (user_id) REFERENCES user(user_id),
FOREIGN KEY (channel_id) REFERENCES channel(channel_id)
);
INSERT INTO user (username, display_name, created_date, about_me, status_msg)
VALUES
('user1#8809', 'TheBuilderJohn', '2023-08-06', 'Crafting and creating, that''s my jam. Building something new every day!', 'Online'),
('user2#1337', 'JaneTheDesigner', '2023-07-20', 'Design is my passion, and I love bringing ideas to life. Let''s create something awesome!', 'Away'),
('user3#0007', 'StudentBob', '2023-09-03', 'Navigating the world of knowledge, one step at a time. Always up for learning something new!', 'Offline'),
('user4#4242', 'AliceWordSmith', '2023-06-15', 'Words are my paintbrush, and I create stories that take you on a journey. Let''s escape reality together!', 'Online'),
('user5#9999', 'MikeThePhotog', '2023-08-12', 'Capturing the beauty in the ordinary. I find magic in the everyday moments.', 'Busy'),
('user6#5555', 'ProfEmily', '2023-05-28', 'Teaching is my superpower! Here to share knowledge and empower minds.', 'Available'),
('user7#2222', 'DaveMelodyMaker', '2023-09-10', 'Music is my language, and I speak it fluently. Let''s jam and make some melodies!', 'Do Not Disturb'),
('user8#6666', 'OliviaGamerGirl', '2023-07-04', 'Conquering virtual worlds and slaying dragons. Just a girl bossing it up in the gaming realm!', 'Online'),
('user9#1111', 'WanderlustBen', '2023-08-25', 'I live for new horizons and unexplored paths. Let''s get lost and find ourselves!', 'Idle'),
('user10#0404', 'SophiaFoodie', '2023-06-02', 'Culinary adventures are my favorite! Always on the hunt for new flavors and delicious memories.', 'Away'),
('user11#3333', 'LucasSporty', '2023-09-08', 'Life''s a game, and I''m here to play! Let''s get moving and have some fun!', 'Online'),
('user12#7777', 'AvaFashionista', '2023-07-22', 'Style is my middle name. I bring the runway to the real way!', 'Busy'),
('user13#8080', 'NatureLoverAlex', '2023-05-10', 'I find peace in the great outdoors. Let''s explore and connect with Mother Nature.', 'Available'),
('user14#4040', 'EmmaBookWorm', '2023-08-30', 'Books are my best friends, and I''m always ready for a new adventure between the pages.', 'Offline'),
('user15#2020', 'EthanTechGuru', '2023-06-18', 'Tech is my playground, and I''m here to innovate and create. Let''s build something awesome!', 'Online'),
('user16#6060', 'MiaTheArtist', '2023-09-05', 'Art is my heartbeat. I bring colors and creativity to life.', 'In a meeting'),
('user17#9090', 'OliverMovieBuff', '2023-07-14', 'Movies are my escape, and I''m always ready for a new adventure on the big screen.', 'Do Not Disturb'),
('user18#5050', 'CharlotteFoodie', '2023-08-20', 'I live to eat and explore new flavors. Let''s indulge and create delicious memories!', 'Away'),
('user19#7070', 'WillFitnessGuru', '2023-06-06', 'Fitness is my passion, and I''m here to help you crush your goals. Let''s get moving!', 'Busy'),
('user20#0000', 'LilyMusicSoul', '2023-09-15', 'Music is my soul food. I find harmony in melodies and rhythms.', 'Available');
INSERT INTO friendship (user_1, user_2, friends_since)
VALUES
(3, 2, '2023-08-20'),
(1, 6, '2023-09-01'),
(5, 4, '2023-07-30'),
(2, 8, '2023-08-15'),
(7, 3, '2023-09-10'),
(1, 10, '2023-06-25'),
(9, 5, '2023-08-05'),
(6, 12, '2023-09-08'),
(4, 13, '2023-07-12'),
(8, 11, '2023-08-22'),
(14, 9, '2023-08-30'),
(10, 15, '2023-06-18'),
(11, 16, '2023-09-03'),
(12, 17, '2023-07-28'),
(13, 18, '2023-08-20'),
(15, 19, '2023-06-08'),
(16, 20, '2023-09-12'),
(17, 1, '2023-07-02'),
(18, 11, '2023-08-16');
INSERT INTO server (name, about, created_date)
VALUES
('Gaming Lounge', 'A place for gamers to connect, chat, and play together.', '2023-08-10'),
('Art Corner', 'A creative space for artists to share their work and collaborate.', '2023-07-25'),
('Book Club', 'Discuss your favorite books and discover new reads with fellow bookworms.', '2023-09-05'),
('Fitness Hub', 'Join us for fitness challenges, workout routines, and healthy lifestyle tips.', '2023-06-12'),
('Music Central', 'For music lovers to connect, share playlists, and discuss their favorite artists.', '2023-08-18'),
('Foodie Paradise', 'A community for food enthusiasts to share recipes, restaurant recommendations, and culinary adventures.', '2023-07-08'),
('Tech Talk', 'A space for tech enthusiasts to discuss the latest gadgets, coding projects, and innovations.', '2023-09-01'),
('Nature Explorers', 'Connect with nature lovers, share outdoor experiences, and plan adventures.', '2023-06-20'),
('Movie Buffs', 'Discuss your favorite films, share reviews, and discover hidden cinematic gems.', '2023-08-03'),
('Style Zone', 'A community for fashion enthusiasts to share style tips, trends, and creative looks.', '2023-07-15');
INSERT INTO membership (user_id, server_id, member_since)
VALUES
('user16#6060', 9, '2024-08-15'),
('user11#3333', 7, '2024-07-28'),
('user18#5050', 5, '2024-09-08'),
('user4#4242', 1, '2024-06-18'),
('user13#8080', 10, '2024-08-22'),
('user6#5555', 2, '2024-05-30'),
('user8#6666', 8, '2024-07-10'),
('user19#7070', 4, '2024-08-18'),
('user20#0000', 6, '2024-06-25'),
('user1#8809', 6, '2024-08-12'),
('user12#7777', 7, '2024-07-20'),
('user3#0007', 2, '2024-09-05'),
('user14#4040', 9, '2024-06-14'),
('user5#9999', 3, '2024-08-19'),
('user15#2020', 10, '2024-05-26'),
('user7#2222', 8, '2024-09-01'),
('user2#1337', 4, '2024-07-08'),
('user9#1111', 5, '2024-08-16'),
('user10#0404', 1, '2024-06-22'),
('user11#3333', 2, '2024-08-25'),
('user2#1337', 7, '2024-07-30'),
('user3#0007', 8, '2024-09-12'),
('user4#4242', 9, '2024-06-28'),
('user5#9999', 10, '2024-08-05'),
('user6#5555', 1, '2024-08-18'),
('user7#2222', 3, '2024-07-25'),
('user8#6666', 4, '2024-09-10'),
('user10#0404', 6, '2024-08-27'),
('user1#8809', 7, '2024-05-31'),
('user12#7777', 8, '2024-09-06'),
('user13#8080', 9, '2024-07-14'),
('user14#4040', 10, '2024-08-21'),
('user15#2020', 1, '2024-06-30'),
('user16#6060', 2, '2024-08-10'),
('user17#9090', 3, '2024-07-22'),
('user18#5050', 4, '2024-09-02'),
('user19#7070', 5, '2024-06-16');
INSERT INTO perm_bitmask (description)
VALUES
('Kick Members'),
('Manage Events'),
('Manage Emojis'),
('Manage Webhooks'),
('Mention Everyone'),
('External Emojis'),
('External Stickers'),
('Create Instant Invite'),
('Voice Priority Speaker'),
('Voice Mute Members'),
('Voice Deafen Members'),
('Change Nicknames of Others'),
('Manage Nicknames'),
('Manage Integrations'),
('Use Application Commands'),
('Speak in Threads'),
('Use Threads'),
('Use External Stickers'),
('Use External Emojis'),
('Attach Files'),
('Add Reactions'),
('Priority Speaker in Threads'),
('Read Message History'),
('Send Messages in Threads'),
('Send Text-to-Speech Messages'),
('Embed Links'),
('Attach Files in Threads'),
('Use Application Commands in Threads'),
('Read Messages in Threads'),
('Send Messages in Threads History');
INSERT INTO server_role (user_id, server_id, role_name, perm_bitmask)
VALUES
('user16#6060', 9, 'Member', 191),
('user11#3333', 7, 'Moderator', 383),
('user18#5050', 5, 'Admin', 507),
('user4#4242', 1, 'Member', 63),
('user13#8080', 10, 'Owner', 1023),
('user6#5555', 2, 'Teacher', 255),
('user8#6666', 8, 'Member', 3),
('user19#7070', 4, 'Moderator', 127),
('user20#0000', 6, 'Member', 3),
('user1#8809', 6, 'Member', 3),
('user12#7777', 7, 'Admin', 507),
('user3#0007', 2, 'Member', 3),
('user14#4040', 9, 'Member', 3),
('user5#9999', 3, 'Member', 3),
('user15#2020', 10, 'Moderator', 127),
('user7#2222', 8, 'Member', 3),
('user2#1337', 4, 'Member', 3),
('user9#1111', 5, 'Member', 3),
('user10#0404', 1, 'Member', 3),
('user11#3333', 2, 'Member', 3),
('user2#1337', 7, 'Member', 3),
('user3#0007', 8, 'Moderator', 127),
('user4#4242', 9, 'Member', 3),
('user5#9999', 10, 'Member', 3),
('user6#5555', 1, 'Member', 3),
('user7#2222', 3, 'Member', 3),
('user8#6666', 4, 'Member', 3),
('user10#0404', 6, 'Member', 3),
('user1#8809', 7, 'Member', 3),
('user12#7777', 8, 'Member', 3),
('user13#8080', 9, 'Admin', 507),
('user14#4040', 10, 'Member', 3),
('user15#2020', 1, 'Member', 3),
('user16#6060', 2, 'Member', 3),
('user17#9090', 3, 'Member', 3),
('user18#5050', 4, 'Member', 3),
('user19#7070', 5, 'Member', 3);
INSERT INTO channel (server_id, name, is_voice)
VALUES
(1, 'General', 0),
(1, 'Announcements', 0),
(1, 'Gaming', 1),
(2, 'Art Gallery', 0),
(2, 'Creative Corner', 0),
(3, 'Book Chat', 0),
(3, 'Reading Nook', 0),
(4, 'Fitness Talk', 0),
(4, 'Workout Buddies', 1),
(5, 'Music Lounge', 0),
(5, 'Concert Vibes', 1),
(6, 'Teacher''s Lounge', 0),
(6, 'Classroom', 0),
(7, 'Gaming Lounge', 1),
(7, 'Strategy Corner', 0),
(8, 'Movie Club', 0),
(8, 'Anime Discussion', 0),
(9, 'Foodie Chat', 0),
(9, 'Recipe Exchange', 0),
(10, 'Health & Wellness', 0),
(10, 'Mindfulness Corner', 0);
INSERT INTO message (user_id, channel_id, content, timestamp, has_embed)
VALUES
('user1#8809', 1, 'Hey, how''s everyone doing today?', '2024-08-20 12:30:00', 0),
('user2#1337', 2, 'Check out my latest design! Thoughts?', '2024-07-30 15:45:15', 1),
('user3#0007', 3, 'Any book recommendations for the summer?', '2024-09-10 09:20:45', 0),
('user4#4242', 4, 'Let''s discuss our favorite workouts and routines!', '2024-06-25 18:15:30', 0),
('user5#9999', 5, 'Who''s going to the concert next week?', '2024-08-15 21:40:20', 1),
('user6#5555', 6, 'Lesson plans for the upcoming week are posted!', '2024-05-31 10:05:00', 0),
('user7#2222', 7, 'Who''s up for a gaming session this weekend?', '2024-09-05 14:35:55', 0),
('user8#6666', 8, 'Just finished an anime, anyone else watched it?', '2024-07-20 22:10:12', 0),
('user9#1111', 9, 'Let''s share some recipes for the potluck!', '2024-08-28 11:45:35', 0),
('user10#0404', 10, 'Any mindfulness or relaxation techniques to share?', '2024-06-20 07:30:25', 0),
('user11#3333', 1, 'Anyone up for a game of basketball this weekend?', '2024-08-25 14:20:10', 0),
('user12#7777', 2, 'What are your thoughts on the new fashion trends for fall?', '2024-07-28 19:50:45', 1),
('user13#8080', 3, 'What are your favorite outdoor spots for a peaceful getaway?', '2024-09-03 13:15:20', 0),
('user14#4040', 4, 'Any new book releases or must-reads we should know about?', '2024-06-30 20:40:55', 1),
('user15#2020', 5, 'Tech news and updates! What are your thoughts?', '2024-08-10 17:25:35', 0),
('user16#6060', 6, 'Are there any upcoming art exhibitions or events we should attend?', '2024-05-26 09:10:15', 0),
('user17#9090', 7, 'Strategy games or action games - what''s your preference?', '2024-09-12 16:35:40', 0),
('user18#5050', 8, 'What''s your favorite anime of all time and why?', '2024-07-14 21:25:05', 0),
('user19#7070', 9, 'Anyone have healthy snack ideas for on-the-go?', '2024-08-22 10:15:50', 0),
('user20#0000', 10, 'Do you have any meditation techniques or apps that you swear by?', '2024-06-28 15:40:30', 0),
('user3#0007', 1, 'My book recommendation is "The Great Gatsby".', '2024-09-11 17:55:20', 0),
('user5#9999', 5, 'I''m so excited for the concert! Who else is going?', '2024-08-17 19:30:45', 1),
('user7#2222', 7, 'I prefer strategy games, especially turn-based ones.', '2024-09-06 11:25:15', 0),
('user9#1111', 9, 'Here''s a recipe for a delicious and healthy smoothie!', '2024-08-29 08:20:50', 0),
('user11#3333', 1, 'I''m down for a game of basketball! Let''s set up a time.', '2024-08-26 09:45:30', 0),
('user13#8080', 3, 'I love hiking in the mountains, especially during autumn.', '2024-09-04 15:10:15', 0),
('user15#2020', 5, 'What are your thoughts on the latest smartphone releases?', '2024-08-11 12:45:25', 0),
('user17#9090', 7, 'Action games for me! Fast-paced and thrilling.', '2024-09-13 18:20:40', 0),
('user19#7070', 9, 'Try fruit and nut bars for a healthy and tasty snack!', '2024-08-23 13:35:15', 0),
('user1#8809', 6, 'I agree, "The Great Gatsby" is a classic!', '2024-08-21 10:25:55', 0),
('user4#4242', 4, 'I love yoga and pilates for a well-rounded workout.', '2024-06-26 08:15:45', 0),
('user6#5555', 6, 'Don''t forget to check the lesson plans for next week!', '2024-05-31 14:20:20', 0),
('user8#6666', 8, 'I''m a fan of "Attack on Titan". It''s an epic adventure!', '2024-07-21 09:35:30', 0),
('user10#0404', 10, 'Breathing exercises and meditation apps help me relax.', '2024-06-21 19:10:50', 0),
('user12#7777', 2, 'I think the new trends are bold and exciting!', '2024-07-29 10:45:15', 1),
('user14#4040', 4, 'I''m excited for the new release from my favorite author!', '2024-06-30 22:15:40', 1),
('user16#6060', 6, 'There''s an art exhibition next weekend, let''s go!', '2024-05-27 12:35:25', 0),
('user18#5050', 8, 'I love the storytelling and animation in "Fullmetal Alchemist".', '2024-07-15 17:40:05', 0),
('user20#0000', 10, 'I find guided meditations and nature sounds very calming.', '2024-06-29 06:20:10', 0),
('user2#1337', 2, 'Thanks for the feedback! Keep an eye out for more designs.', '2024-07-31 10:15:35', 1),
('user11#3333', 1, 'Let''s make it a basketball tournament!', '2024-08-27 11:20:45', 0),
('user15#2020', 5, 'I''m a fan of the new smartphone''s camera features.', '2024-08-12 18:35:10', 0),
('user1#8809', 7, 'I prefer turn-based strategy games too!', '2024-09-07 15:40:25', 0),
('user7#2222', 7, 'Let''s organize a gaming tournament soon!', '2024-09-08 10:15:30', 0),
('user9#1111', 9, 'That smoothie sounds delicious, thanks for sharing!', '2024-08-30 14:25:15', 0),
('user12#7777', 2, 'I agree, the trends are definitely unique and fun!', '2024-07-29 21:05:50', 1),
('user13#8080', 3, 'Mountain hikes are the best, especially with the fall colors.', '2024-09-05 09:35:45', 0),
('user14#4040', 4, 'I can''t wait to get my hands on that new release!', '2024-06-30 22:20:10', 1),
('user16#6060', 6, 'Count me in for the art exhibition!', '2024-05-27 13:10:40', 0),
('user17#9090', 7, 'Let''s do it! Who else is in?', '2024-09-14 11:25:25', 0),
('user18#5050', 8, 'I agree, "Fullmetal Alchemist" is a masterpiece!', '2024-07-16 10:30:20', 0),
('user19#7070', 9, 'Fruit and nut bars sound like a tasty and healthy option!', '2024-08-24 09:45:35', 0),
('user20#0000', 10, 'Guided meditations and nature sounds are a great combo.', '2024-06-29 18:25:45', 0);
INSERT INTO voice_activity (user_id, channel_id, type, timestamp)
VALUES
('user1#8809', 7, 'connect', '2024-08-20 12:35:00'),
('user2#1337', 8, 'connect', '2024-07-30 15:48:15'),
('user3#0007', 9, 'connect', '2024-09-10 09:25:45'),
('user4#4242', 4, 'connect', '2024-06-25 18:20:30'),
('user5#9999', 5, 'connect', '2024-08-15 21:45:20'),
('user6#5555', 6, 'connect', '2024-05-31 10:10:00'),
('user7#2222', 7, 'connect', '2024-09-05 14:40:55'),
('user8#6666', 8, 'connect', '2024-07-20 22:15:12'),
('user9#1111', 9, 'connect', '2024-08-28 11:50:35'),
('user10#0404', 10, 'connect', '2024-06-20 07:35:25'),
('user11#3333', 1, 'connect', '2024-08-25 14:25:10'),
('user12#7777', 2, 'connect', '2024-07-28 19:55:45'),
('user13#8080', 3, 'connect', '2024-09-03 13:20:20'),
('user14#4040', 4, 'connect', '2024-06-30 20:45:55'),
('user15#2020', 5, 'connect', '2024-08-10 17:30:35'),
('user16#6060', 6, 'connect', '2024-05-26 09:15:15'),
('user17#9090', 7, 'connect', '2024-09-12 16:40:40'),
('user18#5050', 8, 'connect', '2024-07-14 21:30:05'),
('user19#7070', 9, 'connect', '2024-08-22 10:20:50'),
('user20#0000', 10, 'connect', '2024-06-28 15:45:30'),
('user1#8809', 7, 'disconnect', '2024-08-20 13:00:15'),
('user3#0007', 9, 'disconnect', '2024-09-10 10:15:25'),
('user5#9999', 5, 'disconnect', '2024-08-15 22:00:40'),
('user7#2222', 7, 'disconnect', '2024-09-05 15:10:35'),
('user9#1111', 9, 'disconnect', '2024-08-28 12:10:15'),
('user11#3333', 1, 'disconnect', '2024-08-25 14:45:50'),
('user13#8080', 3, 'disconnect', '2024-09-03 13:45:40'),
('user15#2020', 5, 'disconnect', '2024-08-10 17:55:25'),
('user17#9090', 7, 'disconnect', '2024-09-12 17:00:10'),
('user19#7070', 9, 'disconnect', '2024-08-22 10:40:20'),
('user20#0000', 10, 'disconnect', '2024-06-28 16:00:45'),
('user2#1337', 8, 'disconnect', '2024-07-30 16:05:20'),
('user4#4242', 4, 'disconnect', '2024-06-25 19:00:10'),
('user6#5555', 6, 'disconnect', '2024-05-31 10:30:45'),
('user8#6666', 8, 'disconnect', '2024-07-20 22:45:30'),
('user10#0404', 10, 'disconnect', '2024-06-20 08:00:15'),
('user12#7777', 2, 'disconnect', '2024-07-28 20:15:25'),
('user14#4040', 4, 'disconnect', '2024-06-30 21:00:30'),
('user16#6060', 6, 'disconnect', '2024-05-26 09:40:20'),
('user18#5050', 8, 'disconnect', '2024-07-14 21:55:15'),
('user1#8809', 1, 'connect', '2024-08-21 11:25:40'),
('user12#7777', 5, 'connect', '2024-07-29 11:35:10'),
('user15#2020', 3, 'connect', '2024-08-11 13:10:25');
-- Friend Recommendations
-- WITH MemberOverlap AS (
-- SELECT
-- m1.user_id AS user_id1,
-- m2.user_id AS user_id2,
-- COUNT(*) AS common_servers
-- FROM
-- membership m1
-- JOIN
-- membership m2 ON m1.server_id = m2.server_id AND m1.user_id != m2.user_id
-- GROUP BY
-- m1.user_id, m2.user_id
-- ),
-- AlreadyFriends AS (
-- SELECT
-- user_1,
-- user_2
-- FROM
-- friendship
-- UNION
-- SELECT
-- user_2,
-- user_1
-- FROM
-- friendship
-- )
-- SELECT
-- u1.username AS User,
-- u2.username AS Recommended_Friend,
-- mo.common_servers
-- FROM
-- MemberOverlap mo
-- JOIN
-- user u1 ON mo.user_id1 = u1.username
-- JOIN
-- user u2 ON mo.user_id2 = u2.username
-- LEFT JOIN
-- AlreadyFriends af ON mo.user_id1 = af.user_1 AND mo.user_id2 = af.user_2
-- WHERE
-- af.user_1 IS NULL AND af.user_2 IS NULL
-- ORDER BY
-- mo.common_servers DESC;
-- WITH ServerActivity AS (
-- SELECT
-- s.server_id,
-- s.name AS server_name,
-- COUNT(DISTINCT m.message_id) AS total_messages,
-- COUNT(DISTINCT v.user_id) FILTER (WHERE v.type = 'connect') AS total_voice_connects
-- FROM
-- server s
-- LEFT JOIN
-- channel c ON s.server_id = c.server_id
-- LEFT JOIN
-- message m ON c.channel_id = m.channel_id
-- LEFT JOIN
-- voice_activity v ON c.channel_id = v.channel_id
-- GROUP BY
-- s.server_id, s.name
-- ),
-- UserActivity AS (
-- SELECT
-- s.server_id,
-- u.username,
-- COUNT(m.message_id) AS message_count,
-- RANK() OVER (PARTITION BY s.server_id ORDER BY COUNT(m.message_id) DESC) AS rank
-- FROM
-- server s
-- JOIN
-- channel c ON s.server_id = c.server_id
-- JOIN
-- message m ON c.channel_id = m.channel_id
-- JOIN
-- user u ON m.user_id = u.username
-- GROUP BY
-- s.server_id, u.username
-- )
-- SELECT
-- sa.server_id,
-- sa.server_name,
-- sa.total_messages,
-- sa.total_voice_connects,
-- ua.username AS top_user,
-- ua.message_count
-- FROM
-- ServerActivity sa
-- JOIN
-- UserActivity ua ON sa.server_id = ua.server_id
-- WHERE
-- ua.rank <= 3
-- ORDER BY
-- sa.server_id, ua.rank;
-- Common Table Expressions (CTEs)
-- -- List of user friendships
-- WITH Friendships AS (
-- SELECT user_1 AS user_id, user_2 AS friend_id FROM friendship
-- UNION
-- SELECT user_2 AS user_id, user_1 AS friend_id FROM friendship
-- ),
-- -- Servers that each user is currently a member of
-- UserServers AS (
-- SELECT user_id, server_id FROM membership
-- ),
-- -- Servers that friends are members of, which the user is not
-- FriendRecommendations AS (
-- SELECT
-- fs.user_id,
-- m.server_id,
-- COUNT(*) AS common_friends_count
-- FROM
-- Friendships fs
-- JOIN
-- UserServers m ON fs.friend_id = m.user_id
-- LEFT JOIN
-- UserServers um ON fs.user_id = um.user_id AND um.server_id = m.server_id
-- WHERE
-- um.server_id IS NULL
-- GROUP BY
-- fs.user_id, m.server_id
-- HAVING
-- common_friends_count > 1 -- More than one friend is in this server
-- )
-- -- Selecting recommended servers for each user
-- SELECT
-- u.username,
-- u.display_name,
-- s.server_id,
-- s.name AS recommended_server,
-- fr.common_friends_count
-- FROM
-- FriendRecommendations fr
-- JOIN
-- user u ON fr.user_id = u.user_id
-- JOIN
-- server s ON fr.server_id = s.server_id
-- ORDER BY
-- fr.user_id, fr.common_friends_count DESC;
SELECT
u.username,
s.name AS server_name,
COUNT(m.message_id) AS total_messages_last_month
FROM
user u
JOIN
membership msh ON u.username = msh.user_id
JOIN
server s ON s.server_id = msh.server_id
JOIN
message m ON m.user_id = u.username AND m.channel_id IN (
SELECT channel_id FROM channel WHERE server_id = msh.server_id
)
LEFT JOIN
server_role sr ON sr.user_id = u.username AND sr.server_id = s.server_id
LEFT JOIN
perm_bitmask pb ON sr.perm_bitmask = pb.perm_num
WHERE
m.timestamp >= DATE('now', '-120 months') AND
(pb.description NOT LIKE '%Admin%' AND pb.description NOT LIKE '%Moderator%') OR pb.description IS NULL
GROUP BY
u.username, s.server_id
HAVING
COUNT(m.message_id) >= 1 AND
(
SELECT COUNT(*)
FROM voice_activity va
WHERE va.user_id = u.username AND va.channel_id IN (
SELECT channel_id
FROM channel
WHERE server_id = s.server_id AND is_voice = 1
) AND va.timestamp >= DATE('now', '-120 months') AND va.type = 'connect'
) >= 1 AND
(
SELECT COUNT(DISTINCT f.user_2)
FROM friendship f
WHERE f.user_1 = u.username AND f.user_2 IN (
SELECT user_id FROM membership WHERE server_id = s.server_id
)
) >= 3
ORDER BY
total_messages_last_month DESC;
To embed this program on your website, copy the following code and paste it into your website's HTML: