Skip to content

Instantly share code, notes, and snippets.

@Lackoftactics

Lackoftactics/client_list.sql

Created May 21, 2019 09:52
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Lackoftactics/c76bb24d38c654f4ec7c658e355d774a to your computer and use it in GitHub Desktop.
Save Lackoftactics/c76bb24d38c654f4ec7c658e355d774a to your computer and use it in GitHub Desktop.
Download ZIP
CLient
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment