Je pense que vous voudriez quelque chose comme :
AND us.first_post_created_at > NOW() - INTERVAL '7 DAYS'
(documentation PostgreSQL sur les dates/heure)
Pour exclure les MP, vous devez effectuer une jointure avec la table topics (en suivant la clé étrangère posts.topic_id) et vérifier la colonne archetype. Ajoutez ceci avant la clause WHERE :
JOIN topics t ON p.topic_id = t.id
…et ceci avant le ORDER BY :
AND t.archetype = 'regular'