DEV Community

Cover image for I Built a PostgreSQL User Analytics System — Here’s What I Learned About Real SQL
Jessica Aki
Jessica Aki

Posted on

I Built a PostgreSQL User Analytics System — Here’s What I Learned About Real SQL

#postgres #beginners #database #sql

Today, I built a User Analytics Database in PostgreSQL. The goal was to simulate a real-world business scenario where we need to answer questions like:

  • How many users registered in the last month?
  • Which countries have the most users?
  • How many users use Gmail, Yahoo, or corporate emails?
  • Who are the youngest and oldest users?

The project helped me combine database design, SQL query practice, and realistic dataset handling into one hands-on exercise.

Tech Stack

  • PostgreSQL
  • SQL
  • Mockaroo (to generate realistic user data)

Database Schema — The users Table 

Column Name Data Type   Constraint
id BIGSERIAL PRIMARY KEY, NOT NULL
first_name VARCHAR(50)  NOT NULL
last_name VARCHAR(50)   
email VARCHAR(150)  UNIQUE
gender VARCHAR(10)  
country VARCHAR(50) 
date_of_birth DATE  
registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Enter fullscreen mode Exit fullscreen mode

This single table already allowed me to answer dozens of meaningful business questions.

Key Queries I Practiced

  • Total Number of Users 
SELECT COUNT(*) AS "Total Users" FROM users;
Enter fullscreen mode Exit fullscreen mode
  • Top 10 Countries by Users 
SELECT country, COUNT(*) AS "Total Users"
FROM users
GROUP BY country
ORDER BY COUNT(country) DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode
  • Gender Distribution 
SELECT gender, COUNT(gender) AS "Total Users"
FROM users
GROUP BY gender
ORDER BY gender;
Enter fullscreen mode Exit fullscreen mode
  • Users Registered in Last 30 Days 
SELECT *
FROM users
WHERE registered_at >= NOW() - INTERVAL '30 days';
Enter fullscreen mode Exit fullscreen mode
  • Oldest & Youngest Users 
-- Oldest
SELECT first_name, last_name, EXTRACT(YEAR FROM AGE(NOW(), date_of_birth)) AS age
FROM users
WHERE date_of_birth = (SELECT MIN(date_of_birth) FROM users);
Enter fullscreen mode Exit fullscreen mode 
-- Youngest
SELECT first_name, last_name, date_of_birth
FROM users
WHERE date_of_birth = (SELECT MAX(date_of_birth) FROM users);
Enter fullscreen mode Exit fullscreen mode
  • Email Provider Analysis 
SELECT COUNT(email)
FROM users
WHERE email ILIKE '%gmail%' OR email ILIKE '%yahoo%' OR email ILIKE '%facebook%';
Enter fullscreen mode Exit fullscreen mode
  • Users Born Between 1990 and 2000 
SELECT *
FROM users
WHERE date_of_birth BETWEEN DATE '1990-01-01' AND '2000-12-31';
Enter fullscreen mode Exit fullscreen mode
  • Countries with More Than 50 Users 
SELECT country, COUNT(*)
FROM users
GROUP BY country
HAVING COUNT(*) > 50;
Enter fullscreen mode Exit fullscreen mode
  • Most Recent 20 Registrations 
SELECT * FROM users ORDER BY registered_at DESC LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

What I Learned This Week

  • Importing real datasets into PostgreSQL using \i and COPY.
  • Executing queries in correct order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
  • Using intervals and EXTRACT to calculate ages and filter by date ranges.
  • Reinforcing constraints, primary keys, and uniqueness for data integrity.
  • Understanding practical SQL for real business insights — not just exercises.

Screenshots / Proof

  • The Schema
    \d users table schema

  • Total Users

  • Total Users in the top 10 countries

  • Gender Distribution

  • Users Registered in the last 30 days

  • Oldest User

  • Youngest User

How to Run This Project Yourself

  • Clone the repository.
  • Open PostgreSQL and connect to your server.
  • Run schema.sql to create the database and table.
  • Run data_import.sql to load Mockaroo-generated users.
  • Run queries.sql to execute analytics queries.

Explore your own analysis and see what insights you can generate.

Final Thoughts

What surprised me most is how extendable this project is. Without changing much, you can:

  • Add an orders or transactions table
  • Link it with a foreign key (user_id)
  • Start answering questions like:
    • Which countries generate the most revenue?
    • Do younger users register more frequently?
    • How long after registration do users become active?

The foundation is already there.

That’s when you realize:

Even “simple” SQL projects can grow into real systems.

Conclusion

This project isn’t advanced and that’s the point. It proves that:

  • You don’t need complex schemas to practice real SQL
  • You don’t need fancy tools to get meaningful insights
  • You can build something useful earlier than you think

I’m continuing to build, extend, and document this system as I learn, one step at a time. I hope to you see you on this journey and I would love to hear your opinions.

Top comments (0)