use examples

/* table creation */

if exists(select * from sim_users)
	drop table sim_users

create table sim_users
(
	userid int,
	revenue int
)

if exists(select * from sim_results)
	drop table sim_results

create table sim_results
(
	runid int, --simulation run id
	userid int, --user id
	churn int, --user churn odds
	revenue_loss float --average revenue per user
)


/* parameter calibration */

declare @users int = 1000 --number of users
declare @churn_rate float = 0.2 --churn rate
declare @apru float = 10 -- average revenue per user (relevant for paying users only)
declare @is_paying_per float = 0.25 --odds for being a paying user

declare @runs int = 1000 --number of simulation cycles\runs

declare @user_ct int = 0 --user creation counter
declare @run_ct int = 0 --simulation loop counter


/* generating user data */
while @user_ct<@users
begin
	insert into sim_users values (@user_ct, case when (RAND(CHECKSUM(NEWID()))<=@is_paying_per) then @apru else 0 end
		);
	set @user_ct= @user_ct + 1
end
;


/* simulation */

while @run_ct<@runs
begin
	insert into sim_results
	select runid, userid, churn, churn*revenue as revenue_loss
	from
		(
		select @run_ct as runid, userid
		, case when(RAND(CHECKSUM(NEWID()))<=@churn_rate) then 1 else 0 end as churn
		, revenue
		from sim_users
		) sim_pars

	set @run_ct = @run_ct + 1
end

/* calculating the percentile of the simulation */
;
with loss_per_run as
(
select runid, count(*) as users, sum(churn) as churns, sum(revenue_loss) as total_revenue_loss
from sim_results
group by runid
)
, churn_ntile as
(
select *, NTILE(1000) OVER(ORDER BY total_revenue_loss DESC) AS churn_percentile
from loss_per_run
)
select *
from churn_ntile