WITH quests AS
  (SELECT *
   FROM
     (SELECT fo.*,
             row_number() over(PARTITION BY id
                               ORDER BY updated_at DESC) AS fo
      FROM pathao_quest.quests AS fo
      WHERE created_at >= '2019-06-01')
   WHERE fo = 1 ),
     transactions AS
  (SELECT *
   FROM
     (SELECT fo.*,
             row_number() over(PARTITION BY id
                               ORDER BY updated_at DESC) AS fo
      FROM pathao_quest.transactions AS fo
      WHERE created_at >= '2019-06-01')
   WHERE fo = 1 ),
     cohorts AS
  (SELECT *
   FROM
     (SELECT fo.*,
             row_number() over(PARTITION BY id
                               ORDER BY updated_at DESC) AS fo
      FROM pathao_quest.cohorts AS fo
      WHERE created_at >= '2019-06-01')
   WHERE fo = 1 ),
     rider_quest_histories AS
  (SELECT *
   FROM
     (SELECT fo.*,
             row_number() over(PARTITION BY id
                               ORDER BY updated_at DESC) AS fo
      FROM pathao_quest.rider_quest_histories AS fo
      WHERE updated_at >= '2019-06-01')
   WHERE fo = 1 ),
     quest_steps AS
  (SELECT *
   FROM
     (SELECT fo.*,
             row_number() over(PARTITION BY id
                               ORDER BY updated_at DESC) AS fo
      FROM pathao_quest.quest_steps AS fo
      WHERE created_at >= '2019-06-01')
   WHERE fo = 1 ),
   rides AS
  (SELECT * FROM
     ( SELECT fo.*,
              row_number() over(PARTITION BY id ORDER BY updated_at DESC) AS fo
                                FROM pathao_ride.rides AS fo
                                WHERE created_at >= '2019-06-01' and updated_at >= '2019-06-01')
        WHERE fo = 1 ),
    templates AS
  (SELECT * FROM
     ( SELECT fo.*,
              row_number() over(PARTITION BY id ORDER BY updated_at DESC) AS fo
                                FROM pathao_quest.templates AS fo
                                WHERE created_at >= '2019-06-01' and updated_at >= '2019-06-01')
        WHERE fo = 1 )


SELECT date(quests.start_date) quest_date,
          quests.start_time,
          quests.end_time,

          rider_quest_histories.quest_id,
          quests.title AS quest_title,
          templates.name as quest_name,
          rider_quest_histories.rider_id,

          quest_steps.target,
          rider_quest_histories.completed_ride,
          rider_quest_histories.total_completed,

          rider_quest_histories.is_achieved,
          transactions.amount,
          transactions.is_paid,

   FROM rider_quest_histories
   LEFT JOIN transactions ON transactions.quest_id = rider_quest_histories.quest_id
   LEFT JOIN cohorts ON cohorts.id = rider_quest_histories.cohort_id
   LEFT JOIN quests ON quests.id = rider_quest_histories.quest_id
   AND cohorts.quest_id = quests.id
   LEFT JOIN quest_steps ON quests.id = quest_steps.quest_id
   AND cohorts.id = quest_steps.cohort_id
   AND rider_quest_histories.completed_step_id = quest_steps.id
   LEFT JOIN templates ON templates.quest_id = rider_quest_histories.quest_id
   WHERE rider_quest_histories.rider_id = transactions.driver_id
     AND rider_quest_histories.completed_step_id != 0
     AND rider_quest_histories.total_completed != 0
     AND rider_quest_histories.is_achieved = 1
     AND transactions.is_paid = 1
     AND amount < 0
     AND Cohorts.name <> "referral"
     AND quests.city_id = 1
     AND rider_quest_histories.rider_id in (select distinct driver_id from rides where ride_type = 2)
     AND date(quests.start_date) BETWEEN '2020-02-02' AND '2020-02-22'
   ORDER BY total_completed DESC