find-users-with-high-token-usage

java

python3

python

javascript

typescript

csharp

golang

kotlin

swift

rust

ruby

dart

scala

elixir

erlang

racket

cangjie

bash

mysql

mssql

postgresql

oraclesql

pythondata

Database

Table: <code>prompts</code>

<pre>
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| prompt | varchar |
| tokens | int |
+-------------+---------+
(user_id, prompt) is the primary key (unique value) for this table.
Each row represents a prompt submitted by a user to an AI system along with the number of tokens consumed.
</pre>

Write a solution to analyze AI prompt usage patterns based on the following requirements:

<ul>
	<li>For each user, calculate the total number of prompts they have submitted.</li>
	<li>For each user, calculate the average tokens used per prompt (Rounded to <code>2</code> decimal places).</li>
	<li>Only include users who have submitted at least <code>3</code> prompts.</li>
	<li>Only include users who have submitted at least one prompt with <code>tokens</code> greater than their own average token usage.</li>
</ul>

Return the result table ordered by average tokens in descending order, and then by <code>user_id</code> in ascending order.

The result format is in the following example.

&nbsp;
Example:

<div class="example-block">
Input:

prompts table:

<pre class="example-io">
+---------+--------------------------+--------+
| user_id | prompt | tokens |
+---------+--------------------------+--------+
| 1 | Write a blog outline | 120 |
| 1 | Generate SQL query | 80 |
| 1 | Summarize an article | 200 |
| 2 | Create resume bullet | 60 |
| 2 | Improve LinkedIn bio | 70 |
| 3 | Explain neural networks | 300 |
| 3 | Generate interview Q&amp;A | 250 |
| 3 | Write cover letter | 180 |
| 3 | Optimize Python code | 220 |
+---------+--------------------------+--------+
</pre>

Output:

<pre class="example-io">
+---------+---------------+------------+
| user_id | prompt_count | avg_tokens |
+---------+---------------+------------+
| 3 | 4 | 237.5 |
| 1 | 3 | 133.33 |
+---------+---------------+------------+
</pre>

Explanation:

<ul>
	<li>User 1:

	<ul>
		<li>Total prompts = 3</li>
		<li>Average tokens = (120 + 80 + 200) / 3 = 133.33</li>
		<li>Has a prompt with 200 tokens, which is greater than the average</li>
		<li>Included in the result</li>
	</ul>
	</li>
	<li>User 2:
	<ul>
		<li>Total prompts = 2 (less than the required minimum)</li>
		<li>Excluded from the result</li>
	</ul>
	</li>
	<li>User 3:
	<ul>
		<li>Total prompts = 4</li>
		<li>Average tokens = (300 + 250 + 180 + 220) / 4 = 237.5</li>
		<li>Has prompts with 300 and 250 tokens, both greater than the average</li>
		<li>Included in the result</li>
	</ul>
	</li>
</ul>

The Results table is ordered by avg_tokens in descending order, then by user_id in ascending order
</div>

find_users_with_high_tokens

Find Users with High Token Usage

表：<code>prompts</code>

<pre>
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| prompt | varchar |
| tokens | int |
+-------------+---------+
(user_id, prompt) 是这张表的主键（值互不相同）。
每一行表示一个用户提交给 AI 系统的提示词以及所消耗的 token 数量。
</pre>

根据下列要求编写一个解决方案来分析 AI 提示词的使用模式：

<ul>
	<li>对每一个用户，计算他们提交的 提示词的总数。</li>
	<li>对每个用户，计算 每个提示词所使用的平均 token 数（舍入到&nbsp;<code>2</code> 位小数）。</li>
	<li>仅包含&nbsp;至少提交了 <code>3</code> 个提示词 的用户。</li>
	<li>仅包含那些 至少提交过一个提示词 且该提示词的 <code>tokens</code> 数量 超过 自己平均 token 使用量的用户。</li>
</ul>

返回结果表按 平均 token 数 降序&nbsp;排序，然后按&nbsp;<code>user_id</code> 升序&nbsp;排序。

结果格式如下所示。

&nbsp;

示例：

<div class="example-block">
输入：

prompts 表：

<pre class="example-io">
+---------+--------------------------+--------+
| user_id | prompt | tokens |
+---------+--------------------------+--------+
| 1 | Write a blog outline | 120 |
| 1 | Generate SQL query | 80 |
| 1 | Summarize an article | 200 |
| 2 | Create resume bullet | 60 |
| 2 | Improve LinkedIn bio | 70 |
| 3 | Explain neural networks | 300 |
| 3 | Generate interview Q&amp;A | 250 |
| 3 | Write cover letter | 180 |
| 3 | Optimize Python code | 220 |
+---------+--------------------------+--------+
</pre>

输出：

<pre class="example-io">
+---------+---------------+------------+
| user_id | prompt_count | avg_tokens |
+---------+---------------+------------+
| 3 | 4 | 237.5 |
| 1 | 3 | 133.33 |
+---------+---------------+------------+
</pre>

解释：

<ul>
	<li>用户 1：

	<ul>
		<li>总提示词数 = 3</li>
		<li>平均 token 数 = (120 + 80 + 200) / 3 = 133.33</li>
		<li>有一个提示词为 200 个 token，这超过了平均值</li>
		<li>包含在结果中</li>
	</ul>
	</li>
	<li>用户 2:
	<ul>
		<li>总提示词数&nbsp;= 2（少于所需的最小值）</li>
		<li>从结果中排除</li>
	</ul>
	</li>
	<li>用户 3:
	<ul>
		<li>总提示词数 = 4</li>
		<li>平均 token 数 = (300 + 250 + 180 + 220) / 4 = 237.5</li>
		<li>有包含 300 和 250 个 token 的提示词，都大于平均数</li>
		<li>包含在结果中</li>
	</ul>
	</li>
</ul>

结果表按 avg_tokens 降序排序，然后按 user_id 升序排序。
</div>