CLient

client_list.sql

EXPLAIN SELECT "customers".*,
       coalesce("customer_bookings"."appointments", 0) AS "appointments",
       coalesce("customer_bookings"."no_shows", 0) AS "no_shows",
       "customer_bookings"."last_appointment" AS "last_appointment",
       coalesce("customer_sales"."total_sales", 0) AS "total_sales",
       greatest("customer_payments_accumulators"."unpaid_amount", 0) AS "outstanding",
       "locations"."name" AS "last_location"
FROM "customers"
LEFT OUTER JOIN
  (SELECT COUNT(*) FILTER(
                          WHERE "bookings"."status" != 'cancelled') AS "appointments",
          COUNT(*) FILTER(
                          WHERE "bookings"."status" = 'no_show') AS "no_shows",
          MAX("bookings"."date") FILTER(
                                        WHERE "bookings"."date" <= '2019-01-10'
                                          OR ("bookings"."date" = '2019-01-10'
                                              AND "bookings"."time_start_in_seconds" < 48057.879041)) AS "last_appointment",
          array_agg("bookings"."location_id"
                    ORDER BY "bookings"."date" DESC) FILTER (
                                                             WHERE "bookings"."date" <= '2019-01-10'
                                                               OR ("bookings"."date" = '2019-01-10'
                                                                   AND "bookings"."time_start_in_seconds" < 48057.879185)) AS "locations",
          "bookings"."customer_id"
   FROM "bookings"
   WHERE "bookings"."provider_id" = 109064
     AND "bookings"."customer_id" IS NOT NULL
   GROUP BY "bookings"."customer_id") "customer_bookings" ON "customer_bookings"."customer_id" = "customers"."id"
LEFT OUTER JOIN
  (SELECT "combined_sales"."customer_id",
          sum("combined_sales"."total_gross" - "combined_sales"."voucher_total_gross") AS "total_sales"
   FROM
     (SELECT "sales"."customer_id",
             "sales"."id",
             "sales"."total_gross" AS "total_gross",
             COALESCE(SUM("sale_items"."total_gross"), 0) AS "voucher_total_gross"
      FROM "sales"
      LEFT JOIN "sale_items" ON "sale_items"."sale_id" = "sales"."id"
      AND "sale_items"."item_type" = 'Voucher'
      WHERE "sales"."provider_id" = 109064
      GROUP BY "sales"."id"
      ORDER BY "sales"."customer_id",
               "sales"."id") "combined_sales"
   GROUP BY "combined_sales"."customer_id") "customer_sales" ON "customer_sales"."customer_id" = "customers"."id"
LEFT OUTER JOIN "locations" ON ("customer_bookings"."locations")[1] = "locations"."id"
AND "locations"."provider_id" = 109064
LEFT OUTER JOIN "customer_payments_accumulators" ON "customers".id = "customer_payments_accumulators"."customer_id" AND "customers"."provider_id" = "customer_payments_accumulators"."provider_id"
WHERE "customers"."deleted_at" IS NULL
  AND ("customers"."provider_id" = 109064)
  AND ("customers"."created_at" BETWEEN '2014-12-31 23:00:00.000000' AND '2019-01-10 22:59:59.999999')
ORDER BY "customers"."updated_at" DESC
LIMIT 100
OFFSET 0
















SELECT "customers".*,
       coalesce("customer_bookings"."appointments", 0) AS "appointments",
       coalesce("customer_bookings"."no_shows", 0) AS "no_shows",
       "customer_bookings"."last_appointment" AS "last_appointment",
       coalesce("customer_sales"."total_sales", 0) AS "total_sales",
       greatest("customer_sales"."outstanding", 0) AS "outstanding",
       coalesce("customer_payments_accumulators"."unpaid_amount", 0) AS "outstanding2",
       "locations"."name" AS "last_location"
FROM "customers"
LEFT OUTER JOIN
  (SELECT COUNT(*) FILTER(
                          WHERE "bookings"."status" != 'cancelled') AS "appointments",
          COUNT(*) FILTER(
                          WHERE "bookings"."status" = 'no_show') AS "no_shows",
          MAX("bookings"."date") FILTER(
                                        WHERE "bookings"."date" <= '2019-01-10'
                                          OR ("bookings"."date" = '2019-01-10'
                                              AND "bookings"."time_start_in_seconds" < 48057.879041)) AS "last_appointment",
          array_agg("bookings"."location_id"
                    ORDER BY "bookings"."date" DESC) FILTER (
                                                             WHERE "bookings"."date" <= '2019-01-10'
                                                               OR ("bookings"."date" = '2019-01-10'
                                                                   AND "bookings"."time_start_in_seconds" < 48057.879185)) AS "locations",
          "bookings"."customer_id"
   FROM "bookings"
   WHERE "bookings"."provider_id" = 109064
     AND "bookings"."customer_id" IS NOT NULL
   GROUP BY "bookings"."customer_id") "customer_bookings" ON "customer_bookings"."customer_id" = "customers"."id"
LEFT OUTER JOIN
  (SELECT "combined_sales"."customer_id",
          sum("combined_sales"."total_gross") AS "total_sales",
          sum("combined_sales"."total_payments") AS "total_payments",
          sum("combined_sales"."total_gross" - "combined_sales"."total_payments") AS "outstanding"
   FROM
     (SELECT "sales"."customer_id",
             "sales"."id",
             "sales"."total_payments",
             SUM("sale_items"."total_gross") AS "total_gross"
      FROM "sales"
      JOIN "sale_items" ON "sale_items"."sale_id" = "sales"."id"
      AND "sale_items"."item_type" != 'Voucher'
      WHERE "sales"."provider_id" = 109064
      GROUP BY "sales"."id"
      ORDER BY "sales"."customer_id",
               "sales"."id") "combined_sales"
   GROUP BY "combined_sales"."customer_id") "customer_sales" ON "customer_sales"."customer_id" = "customers"."id"
LEFT OUTER JOIN "locations" ON ("customer_bookings"."locations")[1] = "locations"."id"
LEFT OUTER JOIN "customer_payments_accumulators" ON "customers".id = "customer_payments_accumulators"."customer_id" AND "customers"."provider_id" = "customer_payments_accumulators"."provider_id"
AND "locations"."provider_id" = 109064
WHERE "customers"."deleted_at" IS NULL
  AND ("customers"."provider_id" = 109064)
  AND ("customers"."created_at" BETWEEN '2013-12-30 23:00:00.000000' AND '2019-01-10 22:59:59.999999')
ORDER BY "customers"."updated_at" DESC
LIMIT 100
OFFSET 0