How to Use SQL for Social Media Data Analysis.

Last Updated : 21 Sep, 2026

A Social Media Data Analysis project uses SQL to analyze social media data and gain insights into user engagement, content performance and audience behavior.

  • Uses MySQL to store and manage social media data.
  • Uses SQL queries to retrieve, filter and analyze data.
  • Supports analysis of users, posts, comments, likes and shares.

Creating the Social Media Database & Tables

In this part, connect to MySQL, create the social media database, define the required tables, insert sample data and verify the database setup.

Steps involved in social media data analysis

There are mainly 3 steps that are required for data analysis. Here is how SQL can help solve social media marketing analytics problems quickly. The 3 steps are:

  • Creating your database and tables
  • Loading your data
  • Analyzing your data using SQL queries
CREATE TABLE posts (
  post_id INTEGER PRIMARY KEY AUTOINCREMENT,
  content VARCHAR(1000) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  likes INT NOT NULL,
  comments INT NOT NULL,
  shares INT NOT NULL
);

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY AUTOINCREMENT,
  full_name VARCHAR(100) NOT NULL,
  username VARCHAR(100) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  followers INT NOT NULL
);

CREATE TABLE interactions (
  interaction_id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INT,
  post_id INT,
  type CHAR(1) NOT NULL CHECK (type IN ('L', 'C', 'S')),
  created_at TIMESTAMP NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (post_id) REFERENCES posts(post_id)
);

Output:

Table posts:

2

Table user:

1

Table interaction:

3

Step 2: Loading your data

There are several ways to load data into a database, such as using SQL, CSV or Excel. Here, we use SQL INSERT queries to load social media data into the tables.

INSERT INTO users (name, username, created_at, followers)
VALUES
('Emma', 'emma123', '2022-01-01 12:00:00', 100),
('Liam', 'liam456', '2022-01-02 14:00:00', 200),
('Sophia', 'sophia789', '2022-01-03 15:00:00', 150),
('Noah', 'noah101', '2022-01-04 16:00:00', 300);

INSERT INTO posts (content, created_at, likes, comments, shares)
VALUES
('Check out this cool new product!', '2022-01-01 13:00:00', 50, 10, 5),
('Had a great day at the beach!', '2022-01-02 14:30:00', 30, 5, 2),
('Just finished reading an amazing book!', '2022-01-03 15:30:00', 45, 8, 3),
('Excited for the weekend!', '2022-01-04 16:30:00', 60, 12, 6);

INSERT INTO interactions (user_id, post_id, type, created_at)
VALUES
(1, 1, 'L', '2022-01-01 13:30:00'),
(2, 2, 'L', '2022-01-02 15:00:00'),
(3, 3, 'C', '2022-01-03 16:00:00'),
(4, 1, 'S', '2022-01-04 17:00:00');

Output:

Table post:

5

Table user:

4

Table interaction:

6

Once you have all of your data loaded up, now you can finally analyze your data by using SQL queries.

Step 3: Analyzing your data using SQL queries

Posts with the most likes:

SELECT content, likes
FROM posts
ORDER BY likes DESC;

Output:

7

The top 3 users with the most followers:

SELECT name, followers
FROM users
ORDER BY followers DESC
LIMIT 3 ;

Output:

8

Percentage of likes, comments & shares for each post:

SELECT content,
       ROUND(CAST(likes AS DECIMAL(10,2)) * 100 /
             (likes + comments + shares), 2) || '%' AS likes,
       ROUND(CAST(comments AS DECIMAL(10,2)) * 100 /
             (likes + comments + shares), 2) || '%' AS comments,
       ROUND(CAST(shares AS DECIMAL(10,2)) * 100 /
             (likes + comments + shares), 2) || '%' AS shares
FROM posts;

Output:

9

You can download the complete project files and SQL scripts from the link below: Social Media Data Analysis.

Comment

Explore