M101P - Week 5

m101p_hw5.1.js

db.posts.aggregate([
  {
    $unwind: "$comments"
  },
  {
    $group: {
      _id: "$comments.author",
      count: {$sum: 1}
    }
  },
  {
    $sort: {count: -1}
  }
]).pretty();

m101p_hw5.2.js

db.zips.aggregate([
  {
    $match: {
      $or: [ {state: "CA"}, {state: "NY"}]
    }
  },
  {
    $group: {
      _id: {
        city: "$city",
        state: "$state"
      },
      pop: {$sum: "$pop"}
    }
  },
  {
    $match: {
      pop: {$gt: 25000}
    }
  },
  {
    $group: {
      _id: "$_id.state",
      pop: {$avg: "$pop"}
    }
  },
  {
    $group: {
      _id: null,
      avg: {$avg: "$pop"}
    }
  }
]);

m101p_hw5.3.js

db.grades.aggregate([
  {
    $unwind: "$scores"
  },
  {
    $match: {
      "scores.type": {$ne: "quiz"}
    }
  },
  {
    $group: {
      _id: {
        class_id: "$class_id",
        student_id: "$student_id"
      },
      avg_student: {$avg: "$scores.score"}
    }
  },
  {
    $group: {
      _id: "$_id.class_id",
      avg_class: {$avg: "$avg_student"}
    }
  },
  {
    $sort: {avg_class: -1}
  }
]);

m101p_hw5.4.js

db.zips.aggregate([
  {
    $project: {
      pop: 1,
      first_char: {$substr : ["$city",0,1]}
    }
  },
  {
    $match: {
      first_char: {$in: ["0","1","2","3","4","5","6","7","8","9"]}
    }
  },
  {
    $group: {
      _id: null,
      total: {$sum: "$pop"}
    }
  }
]);