discounts-statistics.js

db.analytics_general.aggregate([
  {
    $project: {
      date: { $dateFromString: { dateString: "$date" } },
      "new_orders_shemsi-egypt_shopify": 1,
      "new_orders_shemsi-egypt_shopify_units": 1,
      "sales_shemsi-egypt_shopify": 1,
    },
  },
  {
    $project: {
      date: { $dateToString: { format: "%Y-%m-%d", date: "$date" } },
      "new_orders_shemsi-egypt_shopify": 1,
      "new_orders_shemsi-egypt_shopify_units": 1,
      "sales_shemsi-egypt_shopify": 1,
      discount_state: {
        $switch: {
          branches: [
            { label: "Missbasket", date: "2022-03-28" },
            { label: "Hadeer Hameed", date: "2022-04-10" },
            { label: "Nouran Elbanan", date: "2022-06-04" },
            { label: "sarasabry", date: "2022-09-28" },
            { label: "Nour Sherif", date: "2022-10-15" },
            { label: "Angie Muhammad", date: "2022-10-26" },
            { label: "Arielle Elbagoury", date: "2022-10-02" },
            { label: "Alia awwe", date: "2022-11-24" },
            { label: "motherbeing", date: "2023-01-08" },
          ]
            .map((discount) => [
              {
                case: {
                  $and: [
                    {
                      $gte: [
                        "$date",
                        {
                          $dateSubtract: {
                            startDate: ISODate(discount.date),
                            unit: "day",
                            amount: 7,
                          },
                        },
                      ],
                    },
                    { $lt: ["$date", ISODate(discount.date)] },
                  ],
                },
                then: `before ${discount.label}`,
              },
              {
                case: {
                  $and: [
                    {
                      $gte: ["$date", ISODate(discount.date)],
                    },
                    {
                      $lt: [
                        "$date",
                        {
                          $dateAdd: {
                            startDate: ISODate(discount.date),
                            unit: "day",
                            amount: 7,
                          },
                        },
                      ],
                    },
                  ],
                },
                then: `during ${discount.label}`,
              },
              {
                case: {
                  $and: [
                    {
                      $gte: [
                        "$date",
                        {
                          $dateAdd: {
                            startDate: ISODate(discount.date),
                            unit: "day",
                            amount: 7,
                          },
                        },
                      ],
                    },
                    {
                      $lt: [
                        "$date",
                        {
                          $dateAdd: {
                            startDate: ISODate(discount.date),
                            unit: "day",
                            amount: 14,
                          },
                        },
                      ],
                    },
                  ],
                },
                then: `before ${discount.label}`,
              },
            ])
            .flat(),
          default: "no discount",
        },
      },
    },
  },
  {
    $group: {
      _id: "$discount_state",
      start_date: { $first: "$date" },
      "new_orders_shemsi-egypt_shopify": {
        $sum: "$new_orders_shemsi-egypt_shopify",
      },
      "new_orders_shemsi-egypt_shopify_units": {
        $sum: "$new_orders_shemsi-egypt_shopify_units",
      },
      "sales_shemsi-egypt_shopify": {
        $sum: "$sales_shemsi-egypt_shopify",
      },
    },
  },
  { $match: { _id: { $ne: "no discount" } } },
]);