Performance MySQL query with for loop

In this method, first I have to get sundays dates between two dates, in this case about 1 year. Then I go through the dates in a for loop and set them to the query. I use prepared statements to make it faster.

//Get the first day and last day
$dateInitial = strtotime('2018-08-21');
$dateFinal   = strtotime('2019-08-21');
$final       = array();

$sql = "SELECT id_product, product, plant_sowing, plant_production, area_planting, CONCAT(id_product,'_', weeks) AS identity
              FROM (
                    SELECT sw_sowing.id_product, pr_products.product, sw_sowing.type, YEARWEEK(:dates,3) AS weeks, SUM(sw_sowing.quantity) AS plant_sowing,
                           SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6-WEEKDAY(:dates)), :dates), sw_sowing.date)/7)>=sw_sowing.weeks_prod, sw_sowing.quantity,0)) AS plant_production,
                           ((SUM(sw_sowing.quantity))/pr_products.plant_m2) AS area_planting
                    FROM (
                          SELECT MAX(id) AS id
                          FROM sw_sowing
                          WHERE status != 0
                          AND id_tenant = :id_tenant
                          AND date <= :dates
                          AND multiply != 1
                          AND id_product = 1
                          GROUP BY id_production_unit_detail
                    ) AS sw
                    INNER JOIN sw_sowing ON sw_sowing.id = sw.id
                    INNER JOIN pr_products ON pr_products.id = sw_sowing.id_product
                    INNER JOIN pr_varieties ON pr_varieties.id = sw_sowing.id_variety
                    WHERE pr_varieties.code != 1
                    GROUP BY sw_sowing.id_product, sw_sowing.type
                    HAVING type NOT IN('ER','PR')
              ) AS s";

$statement    = $this->db->prepare($sql);

//get the sunday dates between two dates and bind the variables
for ($i = $dateInitial; $i <= $dateFinal ; $i = strtotime('+1 day', $i)) {
        if (date('N', $i) == 7){
          $values = [
            ':dates'      => date("Y-m-d", $i),
            ':id_tenant'  => 1
          ];

          $types = [
            ':dates'      => Column::BIND_PARAM_STR,
            ':id_tenant'  => Column::BIND_PARAM_INT
          ];

          $result   = $this->db->executePrepared($statement, $values, $types);
          $final[]  = $result->fetchAll(PhalconDb::FETCH_ASSOC);
        }
      }
      return $final;

But despite this it is not so fast. The query lasts 10 seconds and I would like it to be faster.

I have also indexed the tables. I would like some opinion on how to best optimize this query or if the way I am doing the query is not adequate.

This is a question that I did before about why I use GROUP BY and MAX(id)

https://stackoverflow.com/questions/52209300/get-max-ids-by-group-mysql