can I write the below query using Over()in mysql?

Question

I am trying to optimise the below sql query but am not finding any way to do it:

SELECT DISTINCT
  (
    SELECT
      COUNT(followers.follower_id)
    FROM
      followers
    WHERE
      followers.follow_start_date <= dates.start_date
  ) AS running_total
, dates.onboarded_date
FROM
   followers
   RIGHT JOIN
   (
     select distinct
       follow_start_date as start_date
     from
       followers
     where
       follow_start_date <= "2020-10-01"
   ) dates
     ON followers.follow_start_date = dates.start_date
ORDER BY
  dates.start_date
;

the above query gives me a running total of followers on day basis. So I am first getting list of unique start dates from table and joining the query that counts followers up until given day.

it gives me output like below:

running_total,start_date

498,2020-08-14 00:00:00

499,2020-08-17 00:00:00

500,2020-09-04 00:00:00

503,2020-09-30 00:00:00

any help to optimise this above query or write the query in a better way to get the same result is appreciated.

Edit: explain query output:

1,PRIMARY,followers,,ALL,,,,,926,100,Using temporary; Using filesort
1,PRIMARY,<derived3>,,ref,<auto_key0>,<auto_key0>,6,follower_app.followers.follow_start_date,10,100,Using index
3,DERIVED,followers,,ALL,,,,,926,33.33,Using where; Using temporary
2,DEPENDENT SUBQUERY,followers,,ALL,,,,,926,3.33,Using where

A better way in mysql or sqlalchemy would be perfect

Answer 1

You know how an owl can turn its head almost completely around? That's what happens to me when I see a RIGHT JOIN. Is this a correct rewrite of your query, using LEFT JOIN?

SELECT  (fs.follow_start_date <= ds.start_date) AS running_total ,
        ds.onboarded_date
    FROM (
        SELECT  distinct follow_start_date as start_date
        from  followers AS fs
        where  follow_start_date <= "2020-10-01" 
         ) dates AS ds
    LEFT JOIN  followers AS fs  ON fs.follow_start_date = ds.start_date
    ORDER BY  ds.start_date;

For performance, that needs

followers:  INDEX(follow_start_date)
dates:  INDEX(start_date)

Hmmm... Something seems to be missing for running_total.

Answer 2

I got the answer:

select distinct date(follow_start_date), count(followers) over ( order by  follow_start_date)
 from followers 
where 
 follow_start_date <='2020-10-01' 

Answer 3

This looks to be the running total you are after:

SELECT
      SUM(COUNT(f.follower_id)) OVER (ORDER BY f.follow_start_date)
          AS running_total,
      f.follow_start_date AS onboarded_date
FROM
      followers AS f
WHERE
      f.follow_start_date <= '2020-10-01'
GROUP BY
      f.follow_start_date
ORDER BY
      f.follow_start_date
;