Les coopérateurs

Les indicateurs du tableau de bord

Date de première inscription des Chouettos à jour de leur cotisation (à l'association)

SELECT count(*) AS "count", CAST("public"."res_partner"."create_date" AS date) AS "create_date"
FROM "public"."res_partner"
WHERE ("public"."res_partner"."barcode" IS NOT NULL
   AND "public"."res_partner"."active" = TRUE)
GROUP BY CAST("public"."res_partner"."create_date" AS date)
ORDER BY CAST("public"."res_partner"."create_date" AS date) ASC

Nouveaux membres par mois (source : Odoo)

SELECT count(*) AS "count", date_trunc('month', CAST("public"."res_partner"."create_date" AS timestamp)) AS "create_date"
FROM "public"."res_partner"
WHERE CAST("public"."res_partner"."create_date" AS date) > CAST(? AS date)
GROUP BY date_trunc('month', CAST("public"."res_partner"."create_date" AS timestamp))
ORDER BY date_trunc('month', CAST("public"."res_partner"."create_date" AS timestamp)) ASC

Panier moyen des Chouettos

SELECT ROW_NUMBER () OVER () AS NUCHOUETTOS, "inner_query2"."sum2"
FROM
(SELECT "inner_query"."partner_id", avg("inner_query"."sum") as sum2
FROM
(SELECT sum("public"."report_pos_order"."price_total") AS "sum", "public"."report_pos_order"."partner_id" AS "partner_id", date_trunc('month', CAST("public"."report_pos_order"."date" AS timestamp)) AS "date"
FROM "public"."report_pos_order"
WHERE partner_id IS NOT NULL
GROUP BY "public"."report_pos_order"."partner_id", date_trunc('month', CAST("public"."report_pos_order"."date" AS timestamp))) AS "inner_query"
WHERE "inner_query"."partner_id" Is Not Null
GROUP BY "inner_query"."partner_id"
ORDER BY sum2 DESC) AS "inner_query2"