Report on the channels in MC that have received messages in the last 2 weeks. Good dynamic SQL example

mc-recent-messages.sql

do $$
	DECLARE r record;
	declare stmt text;
begin
	drop table if exists temp_last_message;
	create table temp_last_message  (chan text, cnt INTEGER);
	FOR r in select c.name::TEXT as chan, dc.local_channel_id as local_channel_id from d_channels dc inner join channel c on c.id = dc.channel_id
	loop 
		stmt := format('INSERT INTO temp_last_message select ''%s'' AS name, count(*) as cnt from d_m%s as messages_last_2_weeks where received_date >= now() - interval ''2 weeks''', r.chan, r.local_channel_id);
		--raise notice 'generated %', stmt;
		execute stmt;
	END loop;
END$$;
 
select * from temp_last_message where cnt > 0 order by cnt DESC;