```
explain (analyze, buffers)
select
bs.person_uuid,
bs.organization_uuid,
s.*
from (
select person_uuid,organization_uuid,geog
from ignitemart.t_person_dim pd
) bs
LEFT OUTER JOIN LATERAL
(
select
count(spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '1 year') as cnty1r1,
count(spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '2 year') as cnty2r1,
max(spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '1 year') as maxy1r1,
max(spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '2 year') as maxy2r1,
min(spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '1 year') as miny1r1,
min(spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '2 year') as miny2r1,
avg(spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '1 year') as avgy1r1,
avg(spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '2 year') as avgy2r1,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '1 year') as pe25y1r1,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '2 year') as pe25y2r1,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '1 year') as medy1r1,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '2 year') as medy2r1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '1 year') as pe75y1r1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '2 year') as pe75y2r1,
stddev_pop(spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '1 year') as vary1r1,
stddev_pop(spent_amount) filter (where ST_DWithin(geog, bs.geog, 500)
and s.activity_date > NOW() - interval '2 year') as vary2r1,
count(spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '1 year') as cnty1r2,
count(spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '2 year') as cnty2r2,
max(spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '1 year') as maxy1r2,
max(spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '2 year') as maxy2r2,
min(spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '1 year') as miny1r2,
min(spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '2 year') as miny2r2,
avg(spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '1 year') as avgy1r2,
avg(spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '2 year') as avgy2r2,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '1 year') as pe25y1r2,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '2 year') as pe25y2r2,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '1 year') as medy1r2,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '2 year') as medy2r2,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '1 year') as pe75y1r2,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '2 year') as pe75y2r2,
stddev_pop(spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '1 year') as vary1r2,
stddev_pop(spent_amount) filter (where ST_DWithin(geog, bs.geog, 1000)
and s.activity_date > NOW() - interval '2 year') as vary2r2,
count(spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '1 year') as cnty1r3,
count(spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '2 year') as cnty2r3,
max(spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '1 year') as maxy1r3,
max(spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '2 year') as maxy2r3,
min(spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '1 year') as miny1r3,
min(spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '2 year') as miny2r3,
avg(spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '1 year') as avgy1r3,
avg(spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '2 year') as avgy2r3,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '1 year') as pe25y1r3,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '2 year') as pe25y2r3,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '1 year') as medy1r3,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '2 year') as medy2r3,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '1 year') as pe75y1r3,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '2 year') as pe75y2r3,
stddev_pop(spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '1 year') as vary1r3,
stddev_pop(spent_amount) filter (where ST_DWithin(geog, bs.geog, 2000)
and s.activity_date > NOW() - interval '2 year') as vary2r3,
count(spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '1 year') as cnty1r4,
count(spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '2 year') as cnty2r4,
max(spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '1 year') as maxy1r4,
max(spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '2 year') as maxy2r4,
min(spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '1 year') as miny1r4,
min(spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '2 year') as miny2r4,
avg(spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '1 year') as avgy1r4,
avg(spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '2 year') as avgy2r4,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '1 year') as pe25y1r4,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '2 year') as pe25y2r4,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '1 year') as medy1r4,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '2 year') as medy2r4,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '1 year') as pe75y1r4,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '2 year') as pe75y2r4,
stddev_pop(spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '1 year') as vary1r4,
stddev_pop(spent_amount) filter (where ST_DWithin(geog, bs.geog, 3000)
and s.activity_date > NOW() - interval '2 year') as vary2r4,
count(spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '1 year') as cnty1r5,
count(spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '2 year') as cnty2r5,
max(spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '1 year') as maxy1r5,
max(spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '2 year') as maxy2r5,
min(spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '1 year') as miny1r5,
min(spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '2 year') as miny2r5,
avg(spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '1 year') as avgy1r5,
avg(spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '2 year') as avgy2r5,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '1 year') as pe25y1r5,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '2 year') as pe25y2r5,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '1 year') as medy1r5,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '2 year') as medy2r5,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '1 year') as pe75y1r5,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '2 year') as pe75y2r5,
stddev_pop(spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '1 year') as vary1r5,
stddev_pop(spent_amount) filter (where ST_DWithin(geog, bs.geog, 4000)
and s.activity_date > NOW() - interval '2 year') as vary2r5
FROM metrics.mdl_engage_actions_table_p1_yaddr_v4 s
WHERE ST_DWithin(geog, bs.geog, 4000)
and bs.person_uuid!=s.person_uuid and s.activity_date > NOW() - interval '2 year')s ON true
limit 200;
```