Top 10% Users By Transcript Volume

MediumSQL · PostgreSQL

Problem

Write a query to find the top 10% of users who generate the most meeting transcripts per week.

Use all available transcript records and exclude transcripts that cannot be associated with a user. Return one deterministic set of top users for each calendar week.

Output

One row per selected user and week, with week_start, user_id, user_name, transcript_count, and weekly_rank.
Include only the top 10% of active users in each week, rounding the required number up to the next whole user.
Sort by week_start, then weekly_rank, then user_id.

Output details

Define each calendar week as Monday through Sunday, with week_start set to that Monday's date.
Sort week_start, weekly_rank, and user_id in ascending order.
Break equal transcript-count ties by ascending user_id before assigning weekly_rank.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Otter.ai user identifier
user_name	VARCHAR(100)	Display name of the user
email	VARCHAR(255)	User email address

meeting_transcripts

Column	Type	Description
transcript_idPK	INT	Unique meeting transcript identifier
user_id	INT	User who generated the transcript
transcript_title	VARCHAR(200)	Title of the meeting transcript
created_at	TIMESTAMP	Timestamp when the transcript was generated

Tablesusersmeeting_transcripts

Write a query to find the top 10% of users who generate the most meeting transcripts per week.

Use all available transcript records and exclude transcripts that cannot be associated with a user. Return one deterministic set of top users for each calendar week.

Output

One row per selected user and week, with week_start, user_id, user_name, transcript_count, and weekly_rank.
Include only the top 10% of active users in each week, rounding the required number up to the next whole user.
Sort by week_start, then weekly_rank, then user_id.

Output details

Define each calendar week as Monday through Sunday, with week_start set to that Monday's date.
Sort week_start, weekly_rank, and user_id in ascending order.
Break equal transcript-count ties by ascending user_id before assigning weekly_rank.