Modeling Buy-Sell Trades with SQL and EMAs — Part 1: Lifecycle Segmentation and Strategy Comparison
This post demonstrates how to transform a trading day ledger into segmented trades using SQL, and compares the performance of a buy-sell model against a buy-and-hold strategy—both across the full evaluation period and during a short-term market shock. It builds on concepts introduced in this prior post on preparing the trading day ledger, and is part of the broader Security Trading Analytics blog focused on lifecycle-aware strategy design and principled security selection.
The raw close prices sourced from Tiingo start on 2022-01-03 and run through 2025-10-23. The ledger contains a succession of trading row values that prescribe when to buy and sell tickers.
This post demonstrates how to process the ledger from the prior post with a SQL model that indicates when to buy and sell tickers. The current post examines how to assign trade_id values to each row in the ledger. Trading days with shared trade_id values denote a trade. By this process, the trading day ledger is transformed into a trades dataset.
The trades dataset represents the behavior of the SQL model that indicates when to buy and sell tickers. This dataset is examined in this post to assess the performance of the SQL buy-sell model across trades. The buy-sell model performance is also compared to a buy-and-hold model over all trading days and over a collection of trading days with a large drawdown.
Trade Segmentation with Lifecycle Integrity
This section presents the T-SQL logic for assigning trade_id values to the trading day ledger, transforming it into a segmented trade dataset. The prior post to the current post displays, but does not persist, a trading day ledger. The columns in the ledger are for ticker, trading date, raw close prices from Tiingo, three EMAs (ema_5, ema_10, and ema_200), buy_signal, own_status, and sell signal. Therefore, this post begins by creating and populating the dbo.ticker_date_close_emas_signals table.
The following three screenshots display cropped selections for the dbo.ticker_date_prices, dbo.ticker_date_ema, and dbo.ticker_date_close_emas_signals tables in the Object Explorer window within SQL Server. These screenshots itemize the columns within each table.
The following SQL logic transforms the trading day ledger into segmented trades. Before reviewing the code, here’s a summary of the lifecycle steps it performs:
Flag trade rows using own_status or sell_signal
Add lag logic to detect transitions into ownership
Assign trade_id using buy_signal and lag logic (to ensure clean trade detection)
Filter for complete trades (must include at least one sell_signal )
Persist segmented trades to dbo.ticker_trade_segments
The following script shows the T-SQL code for ingesting rows from the dbo.ticker_date_close_emas_signals table and returning the dbo.ticker_trade_segments table. The dbo.ticker_date_close_emas_signals table is the persisted version of the trading days ledger developed in the most recently preceding post to this one. The code has three main objectives in addition to implementing the SQL model logic
First to populate a fresh version of the trading days ledger in the dbo.ticker_trade_segments table
Second to add a new column named trade_id between the ticker and date columns in the dbo.ticker_trade_segments table; the trade_id column values for a trade extend from its buy_signal date through to sell_signal date; all trading dates within a trade share the same trade_id value
Third to remove rows for all trading dates that do not have trade_id column value
use [SecurityTradingAnalytics];
go
-- Trade Segmentation with Lifecycle Integrity
-- Assigns trade_id per ticker using ownership transitions and filters for complete trades
-- Output: dbo.ticker_trade_segments
-- Drop and recreate the dbo.ticker_trade_segments table
-- the table contains ticker, trade_id, date, close,
-- ema_5, ema_10, and ema_200 as well as
-- buy_signal, own_status, and sell_signal values
-- displays the trade_id, entry_date, exit_date
-- and trade days for each trade segment
-- trade_id values start from 1 for each ticker
-- create a fresh version of the dbo.ticker_trade_segments table
if object_id('dbo.ticker_trade_segments', 'U') is not null
drop table dbo.ticker_trade_segments;
go
create table dbo.ticker_trade_segments (
ticker varchar(10),
trade_id int,
date date,
[close] float,
ema_5 float,
ema_10 float,
ema_200 float,
buy_signal bit,
own_status bit,
sell_signal bit,
primary key (ticker, trade_id, date)
);
go
-- Step 1: Flag rows that are part of a trade
with filtered as (
select *,
case
when own_status = 1 or sell_signal = 1 then 1
else 0
end as in_trade
from dbo.ticker_date_close_emas_signals
),
-- Step 2a: Add lag_own_status
numbered_lag as (
select
f.*,
coalesce(lag(f.own_status) over (partition by f.ticker order by f.date), 0) as lag_own_status
from filtered f
),
-- Step 2b: Assign trade_id when buy_signal = 1 and lag_own_status = 0
numbered as (
select
nl.*,
sum(case
when nl.buy_signal = 1 and nl.lag_own_status = 0 then 1
else 0
end) over (partition by nl.ticker order by nl.date rows unbounded preceding) as trade_id
from numbered_lag nl
),
-- Step 3: Identify complete trades (those with at least one sell_signal)
complete_trades as (
select ticker, trade_id
from numbered
group by ticker, trade_id
having sum(sell_signal) > 0
),
-- Step 4: Filter only rows that are part of complete trades
final_trades as (
select
n.ticker,
n.trade_id,
n.date,
n.[close],
n.ema_5,
n.ema_10,
n.ema_200,
n.buy_signal,
n.own_status,
n.sell_signal
from numbered n
join complete_trades c
on n.ticker = c.ticker and n.trade_id = c.trade_id
where n.in_trade = 1
)
-- Step 5: Insert into the target table
insert into dbo.ticker_trade_segments (
ticker, trade_id, date, [close], ema_5, ema_10, ema_200,
buy_signal, own_status, sell_signal
)
select
ticker,
trade_id,
date,
[close],
ema_5,
ema_10,
ema_200,
buy_signal,
own_status,
sell_signal
from final_trades
order by ticker, trade_id, date;
-- optionally display the trading days within each ticker
select * from dbo.ticker_trade_segments
-- return summary of ticker, trade_id values along with
-- entry_date, exit_date, and number of trade days
-- per ticker/trade_id combo
select ticker, trade_id, min(date) as entry_date, max(date) as exit_date, count(*) as trade_days
from dbo.ticker_trade_segments
group by ticker, trade_id
order by ticker, trade_id;
The following two screenshots display, respectively, the first and last twenty trading days in the dbo.ticker_trade_segments table.
Notice the first screenshot shows trading day rows for the QQQ ticker that have trade_id values of 1 through 4. Each set of rows for a trade_id value begins with a row having buy_signal value of 1 and ends with a sell_signal value of 1.
The second shows the last twenty rows in the dbo.ticker_trade_segments table. All the rows for this screenshot are for the TQQQ ticker. The first row in this screenshot does not have a buy-signal value of 1. This is because first row for the seventy-third trade occurs prior to 2025-09-19, which is the date for the first row in the screen shot.
The trade_id rows for the SPXL and SPY tickers occur between the last QQQ ticker row and the first TQQQ ticker row.
Comparing Buy-Sell Model Versus Buy-and-Hold Strategy Overall Returns
Whenever you build a model, an obvious question to ask is: how does the buy-sell model compare to just buying and holding a ticker throughout an evaluation period? This section answers that question for the buy-sell model reviewed in the previous section and the preceding post.
One common way to evaluate overall performance is to compute the percent change in price from a beginning trade date to an ending trade date. When basing your analyses on historical data as in this post, all the approaches you are evaluating should be compared over a common time span. The historical data for this post resides in two SQL Server tables.
The dbo.ticker_date_prices table contains a dataset for a buy and hold strategy because it tracks all close prices over sequential trading days.
The dbo.ticker_trade_segments table contains a dataset for a buy-sell strategy. It only includes trading days that are from within a trade. As you can see from the preceding screenshot, the TQQQ ticker was bought and sold a total of 75 times. The close value for the current trading day versus the EMA values for the current trading day is a major determinant of whether a ticker share is bought or held versus whether a ticker share is sold on a trading day.
The comparisons in this post use the dbo.ticker_trade_segments table to determine the beginning and ending dates for each ticker price comparison across both buy-sell and buy-and-hold strategies. Because the dbo.ticker_trade_segments table is derived from the dbo.ticker_date_prices table, the comparisons across dates will be the same for both strategies.
In contrast, the price data for comparisons will depend on the strategy.
o For the buy-and-hold strategy, the beginning and ending prices are derived from the dbo.ticker_date_prices table.
o For the buy-sell model, the beginning and ending prices are derived from the dbo.ticker_trade_segments table.
The following pair of tables show the percent change values by ticker from the buy-and-hold strategy on the left and from the buy-sell model on the right. Each table shows percent change by ticker. There is no difference between the two tables. In other words, for these tickers and time spans both trading methods return identical outcomes. The finding of identical outcomes confirms that buy-sell model can achieve the same returns as the buy-and-hold strategy.
The two trading methods can yield substantially different results for subsets of the overall set of trading days (see the next section for details on this). The outcomes depend on the time span being examined and what is happening during the time span.
Neither the buy-sell model nor the buy-and-hold strategy necessarily reflects what an actual trader would do when using either method as a guide. For example, a trader might easily obtain poorer results with the buy-and-hold strategy by selling during a severe, extended downturn. If a buy-sell model accurately detects short-term price changes, traders following its guidance can avoid trading during extended downturns, In contrast, traders following a buy-and-hold strategy may give into the pain of price declines during an extended downturn and negatively impact their trading balance.
A Short-term Downturn Example
A short-term downturn impacted the US stock market during April, 2025, which is within the time span tracked within this post. This downturn appears to be correlated with the introduction of President Trump’s baseline and country-specific tariffs. President Trump crafted the tariffs to remedy what he assessed to be unfair and unacceptable trade imbalances with United States trading partners. Wikipedia offers an extended description of the tariffs and a timeline of associated events.
For the purposes of this post, three dates are particularly important.
On April 2, 2025, President Trump announced sweeping tariffs in a Rose Garden speech.
On April 5, 2025, Baseline 10% tariff took effect on imports from nearly all countries.
On April 9, 2025, Country-specific tariffs were scheduled to begin, but some were delayed for negotiations.
Traders following the guidelines of a buy-and-hold strategy were fully exposed during the early days of April 2025. In contrast, traders following the guidelines of a buy-sell model similar to the one examined in this post, were not exposed to the April 2025 downturn. This is because the buy-sell model designated no trade_id values for April 2025.
To help evaluate the impact of the actions that took place during the preceding three dates, the following query was run. Notice the query compares the buy-and-hold decline to the buy-sell model decline for the time span from January 2, 2025 through April 7, 2025.
use SecurityTradingAnalytics
go
-- Percent decline from Jan 2 to April 7, 2025 for both raw and segmented prices
select
ticker,
round((1 - cast(raw_exit as float) / cast(raw_entry as float)) * 100, 2) as buy_and_hold_decline,
round((1 - cast(seg_exit as float) / cast(seg_entry as float)) * 100, 2) as buy_sell_model_decline
from (
select
'SPY' as ticker,
-- Raw prices from ticker_date_prices
(select [close] from dbo.ticker_date_prices where ticker = 'SPY' and date = '2025-01-02') as raw_entry,
(select [close] from dbo.ticker_date_prices where ticker = 'SPY' and date = '2025-04-07') as raw_exit,
-- Segmented prices from ticker_trade_segments
(select [close] from dbo.ticker_trade_segments where ticker = 'SPY' and date = '2025-01-02') as seg_entry,
(select [close] from dbo.ticker_trade_segments where ticker = 'SPY' and date = '2025-04-07') as seg_exit
union all
select
'SPXL',
(select [close] from dbo.ticker_date_prices where ticker = 'SPXL' and date = '2025-01-02'),
(select [close] from dbo.ticker_date_prices where ticker = 'SPXL' and date = '2025-04-07'),
(select [close] from dbo.ticker_trade_segments where ticker = 'SPXL' and date = '2025-01-02'),
(select [close] from dbo.ticker_trade_segments where ticker = 'SPXL' and date = '2025-04-07')
union all
select
'QQQ',
(select [close] from dbo.ticker_date_prices where ticker = 'QQQ' and date = '2025-01-02'),
(select [close] from dbo.ticker_date_prices where ticker = 'QQQ' and date = '2025-04-07'),
(select [close] from dbo.ticker_trade_segments where ticker = 'QQQ' and date = '2025-01-02'),
(select [close] from dbo.ticker_trade_segments where ticker = 'QQQ' and date = '2025-04-07')
union all
select
'TQQQ',
(select [close] from dbo.ticker_date_prices where ticker = 'TQQQ' and date = '2025-01-02'),
(select [close] from dbo.ticker_date_prices where ticker = 'TQQQ' and date = '2025-04-07'),
(select [close] from dbo.ticker_trade_segments where ticker = 'TQQQ' and date = '2025-01-02'),
(select [close] from dbo.ticker_trade_segments where ticker = 'TQQQ' and date = '2025-04-07')
) as price_pairs;
The result set from the preceding query appears in the following screenshot. Notice the buy-and-hold declines range from a high of 47.54 percent for the TQQQ ticker down to a low of 13.73 for the SPY ticker. The buy-sell model percent declines were always NULL because the model never designated any trades for Apri 2025. These results point to the power of the buy-sell model for by-passing downturns relative to a buy-and-hold strategy.
Concluding Comments
This post begins by persisting a trading day ledger from a prior post as the dbo.ticker_date_close_emas_signals table in the current post. The dbo.ticker_date_close_emas_signals table is then processed into a ticker_trade_segments table. The processing identifies trading days that are within a trade from the first day of a trade through the final day of a trade. The trading days within a trade are assigned a sequential trade_id number starting from 1.The code within this post processes each of the four tickers separately. For example, the trade_id values for each ticker start at 1.
The ticker_trade_segments table contents represent the outcomes of the buy-sell model being analyzed within this post. The buy-sell model outcomes are dependent on the relationships between the close value for a trading day, five, ten, and two-hundred day EMA values, and three indicators (a buy signal, an own-status indicator, and a sell signal). The trade_id column values in the ticker_trade_segments table are what makes the buy-sell model distinct from a buy-and-hold strategy.
Beyond just sharing the code for implementing the buy-sell model, this post compares the overall return outcomes from the buy-sell model versus return outcomes from the buy-and-hold strategy. The overall returns were identical across the two approaches.
Another performance comparison during a short-term downturn in the market in April, 2025 yielded superior performance for the buy-sell model versus the buy-and-hold strategy. The buy-sell model was more successful because it did not enter a trade during the early days of President Trump’s tariff announcements. So maybe it is wise to override a buy-and-hold strategy occasionally. However, you should understand that when you override a buy-and-hold strategy to accommodate exceptional market turbulence, you are no longer following a buy-and-HOLD strategy.
Comments
Post a Comment