temporal-tabular-reasoning

name	temporal tabular reasoning
description	Use this skill when the user wants SQL tasks where time is the main source of difficulty, including colloquial shift-based time references. Trigger it for requests like “make it about first vs last”, “events that happened before the night shift”, “test recency and ordering”, or “during the lunch hour bounds.”

Skill: temporal tabular reasoning

1. Capability Definition & Real Case

• Professional Definition: The ability to interpret and execute time-sensitive reasoning over relational data, including temporal ordering, semantic shift mapping, recency, interval comparisons, and normalizing colloquial chronological expressions into strict database temporal constants.
• Dimension Hierarchy: Query Reasoning->Relational and Logical Composition->temporal tabular reasoning

Real Case

[Case 1]

• Initial Environment: A medal-history database stores athlete names, years, tournaments, medal outcomes, and event timestamps. The schema supports grouping and temporal filtering.
• Real Question: In which year did Áron Szilágyi achieve his personal highest number of gold medal wins?
• Real Trajectory: Filter to gold medals, group by year, count wins per year, and then rank those yearly counts to identify the peak statistical year.
• Real Answer: 2022
• Why this demonstrates the capability: The question requires more than retrieving all rows for one athlete. The agent must group by year, count wins per year, and then rank those yearly counts identifying the peak year. Because the key logic is temporal grouping and comparison, it directly tests temporal tabular reasoning.

---

[Case 2]

• Initial Environment: A sports-results environment stores athlete birth information and dated medal events. The answer depends on aligning event time to athlete age.
• Real Question: At what age did Michael Phelps win his most recent Olympic Gold Medal?
• Real Trajectory: Establish the most recent event date in a subquery, join to birth tables, and derive the exact age interval at that bounded snapshot.
• Real Answer: The SQL must identify the most recent qualifying gold-medal event first and only then compute age at that exact temporal boundary.
• Why this demonstrates the capability: This task is easy to get wrong if the order of temporal operations is reversed. A model that computes age on the wrong event or ignores the recency constraint will still produce fluent SQL but the wrong answer.

---

[Case 3]

• Initial Environment: An Industrial IoT database stores acoustic events in a 'machine_logs' table containing start_time and loudness, while a domain definition dictates 'Day Shift' operates from 08:00:00 to 16:00:00.
• Real Question: Were there any stamping machine sounds detected during the first two hours of the day shift?
• Real Trajectory: Identify the stamping machine entity, map the day shift to 08:00:00, execute arithmetic to add two hours producing 10:00:00, and filter via explicit BETWEEN clauses.
• Real Answer: SELECT COUNT(*) FROM machine_logs WHERE event_name = 'stamping machine' AND start_time BETWEEN '08:00:00' AND '10:00:00';
• Why this demonstrates the capability: This requires mapping the domain concept 'day shift' and calculating explicit HH:MM:SS literals dynamically contextually. It perfectly probes the agent's ability to enforce strict chronological invariants from relative natural language requests.

Pipeline Execution Instructions

To synthesize data for this capability, you must strictly follow a 3-phase pipeline. Do not hallucinate steps. Read the corresponding reference file for each phase sequentially:

1. Phase 1: Environment Exploration Read the exploration guidelines to discover raw knowledge seeds: references/EXPLORATION.md

2. Phase 2: Trajectory Selection Once Phase 1 is complete, read the selection criteria to evaluate the trajectory: references/SELECTION.md

3. Phase 3: Data Synthesis Once a trajectory passes Phase 2, read the synthesis instructions to generate the final data: references/SYNTHESIS.md