Joins weekly averages with prev compared to current week

averages.sql

create table user_stats 
(   
    ts timestamp,
    username  symbol,
    name  symbol,
    ip_address  symbol,
    post_count  int
)
timestamp(ts);

insert into user_stats values(to_timestamp('2021-04-03T16:25:30.000000'), 'bsmth', 'brian', '1.2.3.4', 100);
insert into user_stats values(to_timestamp('2021-04-03T16:25:30.000000'), 'apela', 'alexp', '1.1.1.1', 1);
insert into user_stats values(to_timestamp('2021-04-04T16:25:30.000000'), 'bsmth', 'brian', '1.2.3.4', 200);
insert into user_stats values(to_timestamp('2021-04-04T16:25:30.000000'), 'apela', 'alexp', '1.1.1.1', 3 );
insert into user_stats values(to_timestamp('2021-04-14T16:25:30.000000'), 'bsmth', 'brian', '1.2.3.4', 10  );
insert into user_stats values(to_timestamp('2021-04-14T16:25:30.000000'), 'apela', 'alexp', '1.1.1.1', 500 );
insert into user_stats values(to_timestamp('2021-04-15T16:25:30.000000'), 'bsmth', 'brian', '1.2.3.4', 20  );
insert into user_stats values(to_timestamp('2021-04-15T16:25:30.000000'), 'apela', 'alexp', '1.1.1.1', 1000);

WITH prev_week AS 
(
SELECT avg(post_count) posts, username FROM user_stats
WHERE ts >= dateadd('d', -14, now()) AND ts <= dateadd('d', -7, now())
GROUP BY username
)
SELECT  
        user_stats.username,
        avg(user_stats.post_count) current_week_avg,
        prev_week.posts prev_week_avg
FROM user_stats JOIN prev_week on (username) 
WHERE user_stats.ts > dateadd('d', -7, now())

Should return

user	this_week_avg	prev_week_avg
brian	15	150
alex	750	2