---
name: pull-contest-launches
description: Extract launches, stats, and maker data from production DB for a contest or topic day
---

# Pull Contest Launches

Extract featured launches with stats, rankings, and maker data for a given contest or topic day. Primary output is a TSV file; optionally syncs to Google Sheets.

## Usage

```
/pull-contest-launches <contest-slug>
/pull-contest-launches <topic-slug> <date>
```

**Examples:**
- `/pull-contest-launches yc-application-day`
- `/pull-contest-launches yc-application 2026-03-12`

## Steps

### 1. Resolve the contest

If given a contest slug, look it up in `config/contests.yml`:
- Read the `launch_tag` (topic slug) and `date` fields
- If the contest uses a `posts:` list instead of `launch_tag:`, use that list of slugs directly (adjust SQL accordingly)

If given a topic slug + date directly, use those.

### 2. Get the topic ID

Run via `bin/rails_prod_readonly`:

```ruby
topic = Topic.find_by!(slug: '<topic-slug>')
puts topic.id
```

### 3. Run the counts query

Using the topic ID and date, run **Query 1** from `queries.sql` (in this skill's directory) to get total/featured/unfeatured counts. Run via `bin/rails_prod_readonly`:

```ruby
sql = <<~SQL
  SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE p.featured = true) AS featured,
    COUNT(*) FILTER (WHERE p.featured = false) AS unfeatured
  FROM posts p
  JOIN post_topic_associations pta ON pta.post_id = p.id AND pta.topic_id = #{topic_id}
  WHERE p.scheduled_at::date = '#{date}'
    AND p.trashed_at IS NULL
SQL
ActiveRecord::Base.connection.execute(sql).first
```

Print the counts: `"X total launches (Y featured, Z unfeatured)"`

### 4. Run the main launches query

Run **Query 2** from `queries.sql` to get featured launches with ranking, stats, and maker data. Replace `$TOPIC_ID` and `$DATE` with actual values.

Run the full query via `bin/rails_prod_readonly` using `ActiveRecord::Base.connection.execute(sql)`.

The query joins:
- `posts` → `post_topic_associations` (topic filtering)
- `product_makers` → `users` (founder names)
- `users_linkedin_verifications` (verified LinkedIn, state='approved')
- `users_work_email_verifications` (verified emails, state='verified')
- `users_links` (additional LinkedIn from profile links, kind='linkedin')
- `product_post_associations` → `products` (company website URL)
- Falls back to `posts.user_id` for posts without product_makers

### 5. Generate TSV output

Create a TSV file with these columns (must match this exact order):

```
Product  URL  Tagline  Founders  Rank  Upvotes  Click-throughs  Comments  Ranking  Assessment  Concerns  RFS  Team Background  LinkedIn  YC Status Notes  Founder Emails  Company URL  Notes
```

- Columns from the DB query: Product, URL, Tagline, Founders, Rank, Upvotes, Click-throughs, Comments, LinkedIn, Founder Emails, Company URL
- Columns left empty for later enrichment: Team Background, YC Status Notes
- Columns left empty for report generation: Ranking, Assessment, Concerns, RFS, Notes

Save the TSV to the working directory as `<contest-slug>-launches.tsv` (e.g., `yc-application-day-launches.tsv`).

### 6. Summary

Print a summary with:
- Total / featured / unfeatured counts
- Number of launches with LinkedIn data
- Number of launches with verified emails
- Path to the TSV file

### 7. Sync to Google Sheet (optional)

If the user requests it, or passes `--sheet`, upload the TSV to a Google Sheet:

```bash
bundle exec .claude/skills/google-sheets/sheets.rb find_or_create "<Contest Name> - Launches"
bundle exec .claude/skills/google-sheets/sheets.rb upload <SPREADSHEET_ID> "Launches" /path/to/<contest-slug>-launches.tsv
```

Print the spreadsheet URL when done. This step is not required — the TSV is the primary artifact.

## Output Columns Reference

| Column | Source | Description |
|--------|--------|-------------|
| Product | `posts.name` | Launch name |
| URL | `posts.slug` | Full PH launch URL |
| Tagline | `posts.tagline` | One-line description |
| Founders | `users.name` via `product_makers` | Comma-separated maker names |
| Rank | `ROW_NUMBER()` by `daily_rank` | Contest ranking position |
| Upvotes | `posts.votes_count` | Total upvotes |
| Click-throughs | `posts.link_unique_visits` | Unique click-throughs to product |
| Comments | `posts.comments_count` | Number of comments |
| Ranking | _(empty)_ | Filled by generate-launch-report |
| Assessment | _(empty)_ | Filled by generate-launch-report |
| Concerns | _(empty)_ | Filled by generate-launch-report |
| RFS | _(empty)_ | Filled by generate-launch-report |
| Team Background | _(empty)_ | Filled by enrich-launches |
| LinkedIn | `users_linkedin_verifications` + `users_links` | Verified LinkedIn URLs |
| YC Status Notes | _(empty)_ | Filled by enrich-launches or manually |
| Founder Emails | `users_work_email_verifications` | Verified work emails |
| Company URL | `products.website_url` | Product website |
| Notes | _(empty)_ | Free-form notes |

## Notes

- Only featured launches are included in the detailed output (unfeatured count is reported in stats)
- The ranking uses `daily_rank ASC NULLS LAST, p.id ASC` to match the contest leaderboard
- LinkedIn data comes from two sources: verified LinkedIn profiles (`users_linkedin_verifications` with state='approved') and user profile links (`users_links` with kind='linkedin')
- Posts without any `product_makers` fall back to the post's `user_id` for founder data