Tuesday, October 8, 2013

MongoDB M101P HW5 answers



5.1

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

5.2

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

5.3

db.stud.aggregate([
{$match:{"scores.type":{$in:["exam","homework"]}}},
{$unwind:"$scores"},
{$group:{_id:"$class_id",csall:{$avg:"$scores.score"}}},
{$sort:{csall:-1}},
{$limit:5}
]
)

5.4

db.zips.aggregate([
    {$project: 
     {
  fc: {$substr : ["$city",0,1]},
 city:1,pop:1
     }  
   },
{$match:{fc:{$lte:"9",$gte:"0"}}} ,  
   {
         $group: {
            _id: null,
            allpop: { $sum: "$pop"}
         }
      }
])

1 comment:

  1. I guess query and answer for 5.4 has changed.
    I have changed it to below new query.

    Query:

    db.zips.aggregate([ {$project: { first_char: {$substr : ["$city",0,1]}, pop: "$pop" }}, {$group:{ "_id": "$first_char", tot_pop:{$sum: "$pop"} }}, {$match:{ "_id" : {$in: ["B", "D", "O", "G", "N", "M"]} }}, {$group:{ "_id": null, "tot_pop": {$sum:"$tot_pop"} }} ])



    Answer: 76394871


    ReplyDelete