Breaking News: Grepper is joining You.com. Read the official announcement!

SubQuery MQL

Argha Pratim answered on July 27, 2022 Popularity 5/10 Helpfulness 1/10

answer SubQuery MQL

SubQuery MQL

Comment

db.getCollection('requests').aggregate([
  {
    $lookup: {
      from: 'bookings',
      localField: '_id',
      foreignField: 'requests',
      as: 'bookings',
    },
  },
  {
    $lookup: {
      from: 'campaigns',
      localField: 'meta.campaign',
      foreignField: 'id',
      as: 'campaign',
    },
  },
  { $unwind: { path: '$campaign', preserveNullAndEmptyArrays: true } },
  { $unwind: { path: '$bookings', preserveNullAndEmptyArrays: true } },
  {
    $lookup: {
      from: 'flags',
      localField: 'bookings.flags',
      foreignField: '_id',
      as: 'bookings.flags',
    },
  },
  {
    $group: {
      _id: '$_id',
      bookings: {
        $addToSet: {
          $cond: {
            if: { $not: '$bookings._id' },
            then: '$$REMOVE',
            else: '$bookings',
          },
        },
      },
      campaign: { $first: '$campaign' },
      dateRequest: { $first: '$dateRequest' },
      meta: { $first: '$meta' },
    },
  },
  {
    $group: {
      _id: '$meta.campaign',
      name: { $first: '$campaign.name' },
      requestcount: { $sum: 1 },
      earliestrequest: { $min: '$dateRequest' },
      revenuetotal: { $sum: { $sum: '$bookings.revenue' } },
      bookingcount: { $sum: { $size: '$bookings' } },
      bookingFlags: { $push: '$bookings.flags' },
    },
  },
  {
    $project: {
      _id: 1,
      name: 1,
      requestcount: 1,
      earliestrequest: 1,
      revenuetotal: 1,
      bookingcount: 1,
      quota: {
        $cond: {
          if: { $gte: ['$requestcount', 1] },
          then: {
            $divide: [{ $multiply: ['$bookingcount', 100] }, '$requestcount'],
          },
          else: 0,
        },
      },
      revenueperbooking: {
        $cond: {
          if: { $gte: ['$bookingcount', 1] },
          then: { $divide: ['$revenuetotal', '$bookingcount'] },
          else: 0,
        },
      },
      revenueperrequest: {
        $cond: {
          if: { $gte: ['$requestcount', 1] },
          then: { $divide: ['$revenuetotal', '$requestcount'] },
          else: 0,
        },
      },
      bookingFlags: {
        $reduce: {
          input: '$bookingFlags',
          initialValue: [],
          in: { $concatArrays: ['$$value', '$$this'] },
        },
      },
    },
  },
]);

Popularity 5/10 Helpfulness 1/10 Language python

Source: www.mongodb.com

Tags: mql python subquery

Link to this answer
Share Copy Link

Contributed on Jul 27 2022

Argha Pratim

0 Answers Avg Quality 2/10

SubQuery MQL

Contents

More Related Answers

SubQuery MQL

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.