-2

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

1

3 Answers 3

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.

3
  • no its not right. it doesnt have to be a right join... its works the same even with just join Oct 28, 2020 at 14:56
  • Now that the original query has been fixed, you may want to revise your answer.
    – Andriy M
    Oct 29, 2020 at 12:17
  • If "use OVER()" is the important part, I don't have an answer. In any case, INDEX(follow_start_date) is important.
    – Rick James
    Oct 29, 2020 at 16:30
1

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' 
1

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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.