Mentor-User-Performance-Analysis-SQL-Mini-Project

This repository contains SQL analysis project using a SQL Mentor user submissions dataset. The goal is to explore user performance and platform activity through aggregation, date-based analysis, and ranking queries.

Dataset Overview

The dataset tracks submissions made by users on a learning platform.

Table: user_submissions

Column	Type	Description
id	BIGINT (Auto Increment)	Unique submission ID
user_id	BIGINT	User identifier
question_id	INT	Question identifier
points	INT	Points earned (positive = correct, negative = incorrect)
submitted_at	TIMESTAMP	Submission time
username	VARCHAR(50)	Username

Tech Stack

SQL (MySQL)

Concepts used: GROUP BY, COUNT, SUM, AVG, CASE WHEN, CTE, DENSE_RANK, date functions (DATE, DATE_FORMAT, YEARWEEK)

Database Setup

CREATE DATABASE mentor_db;
USE mentor_db;

DROP TABLE IF EXISTS user_submissions;

CREATE TABLE user_submissions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT,
  question_id INT,
  points INT,
  submitted_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  username VARCHAR(50),
  PRIMARY KEY (id)
);

SELECT * FROM user_submissions;

Questions Solved (SQL Analysis)

Q1) List all distinct users and their stats
Output: user_name, total_submissions, points_earned

SELECT DISTINCT(username) AS user_name,
       COUNT(submitted_at) AS total_submissions,
       SUM(points) AS points_earned
FROM user_submissions
GROUP BY user_name
ORDER BY total_submissions DESC;

Q2) Calculate the daily average points for each user

SELECT 
  DATE_FORMAT(submitted_at, '%d-%m') AS day,
  username,
  AVG(points) AS daily_avg_points
FROM user_submissions
GROUP BY day, username
ORDER BY username;

Q3) Find the top 3 users with the most positive submissions for each day

WITH daily_submissions AS (
  SELECT 
    DATE(submitted_at) AS day,
    username,
    SUM(CASE WHEN points > 0 THEN 1 ELSE 0 END) AS correct_submission
  FROM user_submissions
  GROUP BY DATE(submitted_at), username
),
user_rank AS (
  SELECT
    day,
    username,
    correct_submission,
    DENSE_RANK() OVER (PARTITION BY day ORDER BY correct_submission DESC) AS rnk
  FROM daily_submissions
)
SELECT 
  day,
  username,
  correct_submission
FROM user_rank
WHERE rnk <= 3
ORDER BY day, rnk, username;

Q4) Find the top 5 users with the highest number of incorrect submissions

SELECT 
    username,
    SUM(CASE WHEN points < 0 THEN 1 ELSE 0 END) AS incorrect_submissions,
    SUM(CASE WHEN points > 0 THEN 1 ELSE 0 END) AS correct_submissions,
    SUM(CASE WHEN points < 0 THEN points ELSE 0 END) AS incorrect_submissions_points,
    SUM(CASE WHEN points > 0 THEN points ELSE 0 END) AS correct_submissions_points_earned,
    SUM(points) AS points_earned
FROM user_submissions
GROUP BY username
ORDER BY incorrect_submissions DESC
LIMIT 5;

Q5) Find the top 10 performers for each week

SELECT *
FROM (
  SELECT
    YEARWEEK(submitted_at, 1) AS year_week,
    username,
    SUM(points) AS total_points_earned,
    DENSE_RANK() OVER (
      PARTITION BY YEARWEEK(submitted_at, 1)
      ORDER BY SUM(points) DESC
    ) AS rnk
  FROM user_submissions
  GROUP BY year_week, username
) t
WHERE rnk <= 10
ORDER BY year_week, rnk, username;

Q6) Daily Active Users + Total Submissions

SELECT
  DATE(submitted_at) AS day,
  COUNT(DISTINCT username) AS active_users,
  COUNT(*) AS total_submissions
FROM user_submissions
GROUP BY DATE(submitted_at)
ORDER BY day;

Q7) Correct vs Incorrect count per user

SELECT
  username,
  SUM(CASE WHEN points > 0 THEN 1 ELSE 0 END) AS correct_submissions,
  SUM(CASE WHEN points < 0 THEN 1 ELSE 0 END) AS incorrect_submissions
FROM user_submissions
GROUP BY username
ORDER BY correct_submissions DESC, incorrect_submissions ASC;

Q8) Daily submissions count

SELECT
  DATE(submitted_at) AS day,
  COUNT(*) AS total_submissions
FROM user_submissions
GROUP BY DATE(submitted_at)
ORDER BY day;

Conclusion

This mini project helped me practice real SQL analysis on user submission data. By solving these queries, I learned how to:

• summarize user performance using COUNT, SUM, AVG
• analyze activity by day and week using date functions
• compare correct vs incorrect submissions using CASE WHEN
• build daily/weekly leaderboards using CTE + DENSE_RANK
• generate meaningful insights about top users and platform engagement using grouping and ranking