MySQL Union without Union

I would like to know how many users joined each day over the last 7 days. Something that looks like this

| day         | count  |
| 6/19        | 53     |
| 6/18        | 23     |
| 6/17        | 55     |
| 6/16        | 153    |
| 6/15        | 93     |
| 6/14        | 86     |

I would write a query like this:

SELECT SUBDATE(CURRENT_DATE(), INTERVAL 0 DAY) as `day`, count(*) as count FROM my_table WHERE DATE(created_at) = SUBDATE(CURRENT_DATE(), INTERVAL 0 DAY)
UNION ALL
SELECT SUBDATE(CURRENT_DATE(), INTERVAL 1 DAY) as `day`, count(*) as count FROM my_table WHERE DATE(created_at) = SUBDATE(CURRENT_DATE(), INTERVAL 1 DAY)
UNION ALL
SELECT SUBDATE(CURRENT_DATE(), INTERVAL 2 DAY) as `day`, count(*) as count FROM my_table WHERE DATE(created_at) = SUBDATE(CURRENT_DATE(), INTERVAL 2 DAY)

But imagine that I CANNOT use UNION ALL or UNION and it MUST be in this same table format. How would an SQL noob do this?

Thanks

Answer

The WHERE clause of your query should have the condition that created_at is greater than or equal to the current date minus 6 days and then group by date:

SELECT DATE(created_at) day, COUNT(*) count 
FROM my_table 
WHERE created_at >= SUBDATE(CURRENT_DATE, INTERVAL 6 DAY)
GROUP BY day