Analyze thread stars in psql database

7mo ago

Use `psql` to analyze the `amp` database and figure out how the `thread_stars` table works. i want to query the threads with the most number of stars

The thread_stars table uses a composite primary key (user_id, thread_id) to track which users starred which threads. Here's the query:

SELECT 
    t.id,
    t.data_title as title,
    COUNT(ts.user_id) as star_count
FROM threads t
LEFT JOIN thread_stars ts ON t.id = ts.thread_id
GROUP BY t.id
ORDER BY star_count DESC
LIMIT 20;

Can you also join the user of the original thread so I can see who created it?

Ran tool mcp__postgres__query

mcp__postgres__query

Here's the updated query with thread creator info:

SELECT 
    t.id,
    t.data_title as title,
    COUNT(ts.user_id) as star_count,
    u.first_name,
    u.last_name,
    u.email
FROM threads t
LEFT JOIN thread_stars ts ON t.id = ts.thread_id
LEFT JOIN users u ON t.user_id = u.id
GROUP BY t.id, u.id
ORDER BY star_count DESC
LIMIT 20;