Sign In Sign Up

Analyze thread stars in psql database

mrnugget ·
17d ago
sourcegraph/amp
Public
splash-screen.ts#L39
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 user wants to understand how the thread_stars table works in the amp database and wants to query threads with the most stars. They want me to use psql to analyze this.

I should:

  1. First check the schema of the thread_stars table
  2. Understand the relationships (foreign keys, etc.)
  3. Help them write a query to get threads with the most stars

I'll use the mcp__postgres__query tool to run SQL queries against the database.

I'll analyze the thread_stars table structure and relationships.

postgres • query called with args:
{
  "sql": "SELECT \n    column_name, \n    data_type, \n    is_nullable,\n    column_default\nFROM information_schema.columns \nWHERE table_name = 'thread_stars' \nORDER BY ordinal_position;"
}
postgres • query called with args:
{
  "sql": "SELECT\n    tc.constraint_name,\n    tc.constraint_type,\n    kcu.column_name,\n    ccu.table_name AS foreign_table_name,\n    ccu.column_name AS foreign_column_name\nFROM information_schema.table_constraints AS tc\nJOIN information_schema.key_column_usage AS kcu\n    ON tc.constraint_name = kcu.constraint_name\nLEFT JOIN information_schema.constraint_column_usage AS ccu\n    ON ccu.constraint_name = tc.constraint_name\nWHERE tc.table_name = 'thread_stars';"
}
postgres • query called with args:
{
  "sql": "SELECT COUNT(*) as total_stars FROM thread_stars;"
}
postgres • query called with args:
{
  "sql": "SELECT * FROM thread_stars LIMIT 3;"
}

The thread_stars table is a many-to-many join table with composite primary key (user_id, thread_id). Each row represents one user starring one thread. Here's the query for threads with most stars:

postgres • query called with args:
{
  "sql": "SELECT \n    t.id,\n    t.title,\n    COUNT(ts.user_id) as star_count,\n    t.created_at\nFROM threads t\nLEFT JOIN thread_stars ts ON t.id = ts.thread_id\nGROUP BY t.id, t.title, t.created_at\nORDER BY star_count DESC\nLIMIT 20;"
}
postgres • query called with args:
{
  "sql": "SELECT \n    t.id,\n    COUNT(ts.user_id) as star_count,\n    t.created_at,\n    t.updated_at\nFROM threads t\nLEFT JOIN thread_stars ts ON t.id = ts.thread_id\nGROUP BY t.id\nORDER BY star_count DESC\nLIMIT 20;"
}
postgres • query called with args:
{
  "sql": "SELECT column_name FROM information_schema.columns WHERE table_name = 'threads' ORDER BY ordinal_position;"
}
postgres • query called with args:
{
  "sql": "SELECT \n    t.id,\n    t.data_title as title,\n    COUNT(ts.user_id) as star_count,\n    t.created,\n    t.updated_at\nFROM threads t\nLEFT JOIN thread_stars ts ON t.id = ts.thread_id\nGROUP BY t.id, t.data_title, t.created, t.updated_at\nORDER BY star_count DESC\nLIMIT 20;"
}

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;
splash-screen.ts#L39
Can you also join the user of the original thread so I can see who created it?

The user wants to join the users table to see who created each thread. I need to add a join to the users table using the thread's user_id field.

postgres • query called with args:
{
  "sql": "SELECT \n    t.id,\n    t.data_title as title,\n    COUNT(ts.user_id) as star_count,\n    u.id as creator_id,\n    u.name as creator_name,\n    u.email as creator_email\nFROM threads t\nLEFT JOIN thread_stars ts ON t.id = ts.thread_id\nLEFT JOIN users u ON t.user_id = u.id\nGROUP BY t.id, t.data_title, u.id, u.name, u.email\nORDER BY star_count DESC\nLIMIT 20;"
}