generate-sql

Key Input Parameters

project_id:
- The project ID to query, obtained from context. If not found, ask the user. Do not guess or randomly generate
- By default, only one project's query SQL can be generated at a time. If multiple project IDs need to be queried, generate and execute them one by one
$part_date:
- The date range to query, limiting the query scope based on date partitions
- If querying data for the date range from 2026-01-01 to 2026-01-07, the generated filter condition fragment example is: "$part_date" between '2026-01-01' and '2026-01-07'
- Only required for
$part_event:
- The event range to query. Confirm valid event names by combining context or using ae-cli analysis-meta event list. Do not guess or randomly generate
- If querying a single event 'register', the filter condition fragment example is: "$part_event" in ('register')
- If querying multiple events like 'login' and 'logout', the filter condition fragment example is: "$part_event" in ('login', 'logout')
- Only required for

Main Data Tables in TE System

General SQL Specifications

By default, SQL should include a limit 100 result quantity restriction at the end
Query, filter, group by, and alias fields all need to add " before and after the name to avoid TRINO/PRESTO recognition errors
- Example: select "#user_id", "channel" as "Channel"
- Example: group by "#account_id", "#distinct_id"
Notes on "join keys" in join operations (left join, full join, inner join)
- with
  - Use "#user_id" for joining
  - SQL example: from ta.v_event_8 left join ta.v_event_8 using("#user_id")
  - Common scenarios: Retention analysis (players registered on day T, logged in on T+1), Funnel analysis (proportion of players who completed tutorial step 2 after completing step 1)
- with
  - Use "#user_id" for joining
  - SQL example: from ta.v_event_8 left join ta.v_user_8 using("#user_id")
  - Common scenarios: Join query for "vip_level", "total_pay_amount" and other attributes in
TRINO/PRESTO SQL cannot use aliases from the select fields when using group by logic. Must use original field names or calculation logic, or field position numbers
- Correct example: select "#user_id" as "uid", count("channel") as "ya" from v_event_8 group by "#user_id" /*Use original field name*/
- Correct example: select "#user_id"+1 as "uid", count("channel") as "ya" from v_event_8 group by "#user_id"+1 /*Use original calculation logic*/
- Correct example: select "#user_id"+1 as "uid", count("channel") as "ya" from v_event_8 group by 1 /*Use field position number*/
- Incorrect example: select "#user_id" as "uid", count("channel") as "ya" from v_event_8 group by "uid" /*Error: using alias*/

Common Scenarios and Check Templates

/*Assumption: project id=8, check players registered on 2026-01-01, known registration event=register, event contains channel, device_type, server_id properties
Use case: Based on a certain event, spot check players related to that event
Note: Only retrieves 100 records, adjust limit restriction and re-query to get more data*/
select "#distinct_id", "#account_id", "#event_time", "#event_name", "channel", "device_type", "server_id"
from ta.v_evnet_8 /*Project ID:8*/
where 1=1
and "$part_event" in ('register') /*Event name partition range*/
and "$part_date" between '2026-01-01' and '2026-01-01' /*Event date partition range*/
limit 100

/*Assumption: project id=8, check player (#account_id='yaxiaoliang') register, login, logout behaviors on '2026-01-01'; register and login events contain "channel" property, logout event contains "online_time" property
Use case: For a specific abnormal player, investigate their abnormal event situations within a certain date range. Default result quantity limit is 100, can be adjusted as needed
Note: Only retrieves 100 records, adjust limit restriction and re-query to get more data*/
select "#distinct_id", "#account_id", "#event_time", "#event_name", "channel", "online_time"
from ta.v_evnet_8 /*Project ID:8*/
where 1=1
and "$part_event" in ('register', 'login', 'logout') /*Event name partition range*/
and "$part_date" between '2026-01-01' and '2026-01-01'/*Event date partition range*/
and "#account_id" in ('yaxiaoliang') /*Filter by account ID*/
limit 100

/*Assumption: project id=8, check players who paid on 2026-01-01, also display "accumulated payment amount" from player properties
Known payment event=player_pay, event contains pay_amount (numeric type), item_id (text type)
"Accumulated payment amount" in player properties is acc_pay_amount (numeric type)
Use case: While querying event data, obtain more player-related information from <User Table>
Note: Only retrieves 100 records, adjust limit restriction and re-query to get more data
*/
select tav."#distinct_id", tav."#account_id", tav."#event_time", tav."#event_name", tav."pay_amount", tav."item_id", tau."acc_pay_amount"
from ta.v_evnet_8 tav /*Project ID:8, Event Table*/
left join ta.v_user_8 tau /*Project ID:8, User Table*/
using("#user_id")
where 1=1
and "$part_event" in ('player_pay') /*Event name partition range*/
and "$part_date" between '2026-01-01' and '2026-01-01' /*Event date partition range*/
limit 100

/*Assumption: project id=2, check key event situations for players with channel="suspect_channel" within the range of 2026-03-01 to 2026-03-02
Events include: register, login, activity_attend, player_pay
Calculation results include: player primary key ID info, player account ID info, number of event types triggered by player, total number of events triggered by player, number of times each event was triggered by player (map type)
Display logic: Sort by number of event types triggered by player, display top ten players with the least total quantity
Use case: After locating abnormal players, check if their event behaviors are abnormal. If necessary, can further join with <User Table> for query
Note: Only retrieves 100 records, adjust limit restriction and re-query to get more data
*/
with tbl1 as (
	select "#user_id", "#account_id", "$part_event", count() as "event_count"
	from ta.v_event_2 /*Project ID:2, Event Table*/
	where 1=1
	and "$part_event" in ('register', 'login', 'activity_attend', 'player_pay') /*Event name partition range*/
	and "$part_date" between '2026-03-01' and '2026-03-02' /*Event date partition range*/
	and "channel" in  ('suspect_channel') /*Check scope, currently filtering by channel*/
	group by "#user_id", "#account_id", "$part_event"
)
select
    "#user_id", "#account_id"
    ,count(distinct "$part_event" ) as "event_name_count" /*Number of event types triggered by player, fewer types means more singular behavior and higher abnormality*/
    ,sum("event_count") as "event_sum" /*Total event volume triggered by player*/
    ,map_agg("$part_event", "event_count") as "detail_info" /*Event volume for each event triggered by player, can be used to analyze player behavior concentration*/
from tbl1
group by "#user_id", "#account_id"
order by "event_name_count", "event_sum"
limit 100

Output Format Requirements

After generating SQL statements, check for syntax errors according to trino/presto engine specifications
After confirmation, directly output the SQL statement. SQL statements can have corresponding logic comments. No other explanatory content is needed before or after the SQL statement

About this skill