how to group by sql data in a sub-query to show evolution

I have my sqlite database which contains urls and the number of times it has been visited per week. it’s stored this way :

uid, url, date, nb_visits,...

I would like to get every single URL with the evolution of the number of visits grouped by date. something that could looks like :

 "my_url_1","45,54,76,36,78"

here my assumption is that there are 5 dates stored into the db, don’t need the dates, just want them to be ordered from old to recent I tried something like this, but don’t accept 2 field in the second select

select url, visits,
(select sum(visits) from data d2 where d2.url=d1.url group by date) as evol
from data d1
where date = (select max(date) from data);

This query isn’t working just wanted to share what i’m trying to do Thanks for the help

Answer

What you want is the result of GROUP_CONCAT() for each url.
The problem with the aggregate function GROUP_CONCAT() is that it does not support an ORDER BY clause, which you need to sort the visits by date, so the result that you would get is not guaranteed to be correct.

In SQLite there is also a GROUP_CONCAT() window function which supports an ORDER BY clause and can be used to return the result that you want:

SELECT DISTINCT url,
       GROUP_CONCAT(visits) OVER (
         PARTITION BY url 
         ORDER BY date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) visits
FROM data 

See a simplified demo.