SQL Query for P99 - v0 by Vercel

To provide an accurate SQL query for the P99 of chats sent per user per day, I'll need to refer to the database schema. Let me check the schema information available to me.

After reviewing the schema 1, I can see that we have a Message interface which includes fields like messageId, chatId, and createdAt. However, there doesn't seem to be a direct user ID field in this interface. We might need to join with the Chat table to get the authorId.

Given this information, here's a SQL query that should give you the P99 of chats sent per user per day:

WITH daily_user_chats AS (
  SELECT 
    c.authorId,
    DATE(m.createdAt) AS chat_date,
    COUNT(DISTINCT m.chatId) AS chats_sent
  FROM 
    Message m
    JOIN Chat c ON m.chatId = c.chatId
  GROUP BY 
    c.authorId, 
    DATE(m.createdAt)
)
SELECT 
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY chats_sent) AS p99_chats_per_user_per_day
FROM 
  daily_user_chats;

Here's a breakdown of what this query does:

The CTE (Common Table Expression) daily_user_chats calculates the number of distinct chats each user participates in per day.
We join the Message and Chat tables to get the authorId.
We group by authorId and the date part of createdAt to get daily counts.
In the main query, we use PERCENTILE_CONT to calculate the 99th percentile (P99) of these daily chat counts.

Please note that this query assumes:

The Message table contains individual messages.
The Chat table contains information about each chat, including the authorId.
We're considering a "chat sent" as a unique chat a user participated in on a given day, not individual messages.

If you need to adjust any of these assumptions or if you have more specific requirements, please let me know and I'll be happy to modify the query accordingly.

To provide an accurate SQL query for the P99 of chats sent per user per day, I'll need to refer to the database schema. Let me check the schema information available to me.

Given this information, here's a SQL query that should give you the P99 of chats sent per user per day:

WITH daily_user_chats AS (
  SELECT 
    c.authorId,
    DATE(m.createdAt) AS chat_date,
    COUNT(DISTINCT m.chatId) AS chats_sent
  FROM 
    Message m
    JOIN Chat c ON m.chatId = c.chatId
  GROUP BY 
    c.authorId, 
    DATE(m.createdAt)
)
SELECT 
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY chats_sent) AS p99_chats_per_user_per_day
FROM 
  daily_user_chats;

Here's a breakdown of what this query does:

The CTE (Common Table Expression) daily_user_chats calculates the number of distinct chats each user participates in per day.
We join the Message and Chat tables to get the authorId.
We group by authorId and the date part of createdAt to get daily counts.
In the main query, we use PERCENTILE_CONT to calculate the 99th percentile (P99) of these daily chat counts.

Please note that this query assumes:

The Message table contains individual messages.
The Chat table contains information about each chat, including the authorId.
We're considering a "chat sent" as a unique chat a user participated in on a given day, not individual messages.

If you need to adjust any of these assumptions or if you have more specific requirements, please let me know and I'll be happy to modify the query accordingly.