You are a data analyst at a SaaS company that provides a project management tool. The engineering team logs every user login event to monitor platform engagement. The growth team has asked you to identify the most active users based on how frequently they log into the platform during the current month. This helps the team understand power users who could be candidates for case studies, referral programs, or beta feature testing. You have access to two tables: users , which stores account information including user_id , username , and email ; and login_logs , which records every login event with log_id , user_id (foreign key referencing users), and logged_in_at (a TIMESTAMP of when the login occurred). A single user can appear many times in the login_logs table across different sessions and days. Count the total number of logins for each user during the current calendar month (i.e., same month and year as today). Only include users who have logged in at least once this month. Return the username , email , and the login count aliased as login_count . Sort the results by login_count in descending order , showing the most active users first. Limit the output to the top 5 users . Hint: To filter logins within the current month, use DATE_TRUNC('month', logged_in_at) = DATE_TRUNC('month', CURRENT_DATE) . This safely captures all timestamps within the current month regardless of the day. A common pitfall is filtering with MONTH(logged_in_at) = MONTH(CURRENT_DATE) — this is not valid PostgreSQL syntax. Another mistake is forgetting to also match the year , which would incorrectly include logins from the same month in previous years.

Table Setup (SQL Schema)

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

CREATE TABLE login_logs (
  log_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(user_id),
  logged_in_at TIMESTAMP NOT NULL
);

INSERT INTO users (username, email) VALUES
  ('akash_v', '[email protected]'),
  ('priya_m', '[email protected]'),
  ('rohan_d', '[email protected]'),
  ('sneha_k', '[email protected]'),
  ('nikhil_r', '[email protected]'),
  ('divya_s', '[email protected]'),
  ('arjun_p', '[email protected]');

INSERT INTO login_logs (user_id, logged_in_at) VALUES
  (1, NOW() - INTERVAL '1 day'),
  (1, NOW() - INTERVAL '2 days'),
  (1, NOW() - INTERVAL '3 days'),
  (1, NOW() - INTERVAL '5 days'),
  (1, NOW() - INTERVAL '6 days'),
  (1, NOW() - INTERVAL '7 days'),
  (2, NOW() - INTERVAL '1 day'),
  (2, NOW() - INTERVAL '4 days'),
  (2, NOW() - INTERVAL '8 days'),
  (2, NOW() - INTERVAL '9 days'),
  (2, NOW() - INTERVAL '10 days'),
  (3, NOW() - INTERVAL '2 days'),
  (3, NOW() - INTERVAL '3 days'),
  (3, NOW() - INTERVAL '11 days'),
  (3, NOW() - INTERVAL '12 days'),
  (4, NOW() - INTERVAL '1 day'),
  (4, NOW() - INTERVAL '6 days'),
  (4, NOW() - INTERVAL '13 days'),
  (5, NOW() - INTERVAL '3 days'),
  (5, NOW() - INTERVAL '14 days'),
  (6, NOW() - INTERVAL '5 days'),
  (7, NOW() - INTERVAL '40 days'),
  (7, NOW() - INTERVAL '50 days');