像这样的事？

SELECT Counter.Articles, Counter.Users (
SELECT COUNT(1) FROM tbl_articles WHERE DATE(createdate) BETWEEN (CURRENT_TIMESTAMP() AND DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)) Articles,
SELECT COUNT(1) FROM tbl_users WHERE DATE(createdate) BETWEEN (CURRENT_TIMESTAMP() AND DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)) Users
) Counter;

顺便说一句，我还没有测试过

这并不像只使用group by那么简单，但正如你所认为的，group by是很重要的。我想没有简单的5行查询可以得到你期望的结果。我建议做多个查询和一些后端代码，而不是一个查询。它很难阅读，理解和维护。
但这是可能的。
简单select中的GROUP BY DATE_FORMAT(Created,'%Y-%m-%d')已经给予了每天的计数。
下一个要解决的问题是，你想要的是days_back而不是日期本身。这对DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d'), DATE_FORMAT(Createdate,'%Y-%m-%d'))来说也很简单。
但是第三个要求是困难的。你不需要每天的数量，而是到那一天的总和，对于一个可变的天数。不幸的是，mysql不提供Sequence（如SELECT从1到X的数字）与查询的其他部分连接。如果您可以接受大约300年的限制，https://stackoverflow.com/a/9296238/4675841将在这方面有所帮助。要同时获得文章计数和用户计数，可以使用union（all）或join，我使用了join并提出了以下查询。

SELECT user_query.diff as days_back, article_count as count, user_count as users
FROM (
    SELECT DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d'), gen_date) as diff, COUNT(create_date) as user_count
    FROM (
        SELECT adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date
        FROM
            (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
            (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) possible_dates
        LEFT JOIN (SELECT DATE_FORMAT(Createdate,'%Y-%m-%d') as create_date FROM tbl_users) counts
            ON create_date <= gen_date
        WHERE gen_date BETWEEN DATE_SUB(NOW(), INTERVAL 20 DAY) AND NOW()
        GROUP BY gen_date) user_query
INNER JOIN (
    SELECT DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d'), gen_date) as diff, COUNT(create_date) as article_count
    FROM (
        SELECT adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date
        FROM
            (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
            (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
        LEFT JOIN (SELECT DATE_FORMAT(Createdate,'%Y-%m-%d') as create_date FROM tbl_articles) counts
            ON create_date <= gen_date
        WHERE gen_date BETWEEN DATE_SUB(NOW(), INTERVAL 20 DAY) AND NOW()
    GROUP BY gen_date) article_query
    ON user_query.diff = article_query.diff
ORDER BY days_back

试试看。