Relational modeling

Relational modeling from multiple SQL queries into ER diagram (BETA 🧑🏻‍💻)

-- Total posts by users
SELECT SUM(user_posts.total_posts_per_user)
FROM (SELECT users.username,COUNT(photos.image_url) AS total_posts_per_user
		FROM users
		JOIN photos ON users.id = photos.user_id
		GROUP BY users.id) AS user_posts;
		
-- User ranking by postings
SELECT users.username, COUNT(photos.image_url)
FROM users
JOIN photos ON users.id = photos.user_id
GROUP BY users.id
ORDER BY 2 DESC;

-- User segments based on their followings and the tags
WITH user_segments AS (
    SELECT 
        u.id AS user_id,
        COUNT(DISTINCT f.followee_id) AS following_count,
        COUNT(DISTINCT pt.tag_id) AS tag_count
    FROM 
        users u
    LEFT JOIN follows f ON u.id = f.follower_id
    LEFT JOIN photos p ON u.id = p.user_id
    LEFT JOIN photo_tags pt ON p.id = pt.photo_id
    GROUP BY u.id
)
SELECT 
    user_id,
    CASE 
        WHEN following_count >= 1000 AND tag_count >= 100 THEN 'Influencers'
        WHEN following_count >= 500 AND tag_count >= 50 THEN 'Active Users'
        WHEN following_count >= 100 AND tag_count >= 30 THEN 'Engaged Users'
        ELSE 'Casual Users'
    END AS user_segment
FROM 
    user_segments;

-- Which hashtags are most effective in driving engagements?
WITH tag_engagements AS (
  SELECT 
    t.tag_name,
    pt.photo_id,
    COUNT(l.user_id) AS likes,
    COUNT(c.id) AS comments
  FROM tags t
  JOIN photo_tags pt ON t.id = pt.tag_id
  LEFT JOIN likes l ON pt.photo_id = l.photo_id
  LEFT JOIN comments c ON pt.photo_id = c.photo_id
  GROUP BY t.tag_name, pt.photo_id
)
SELECT
    tag_name,
    AVG(likes + comments) AS avg_engagement,
    COUNT(*) AS hashtag_usage
FROM tag_engagements
GROUP BY tag_name
ORDER BY avg_engagement DESC;

Last updated