CSIS 3300 - Database II

csis3300_queries.js

use('test');


// 1st Query
db.fact_movie_insight.aggregate([
    {
        $match: {
            "movie.content_rating" : {
                $in : [
                    "G", "PG", "PG-13"
                ]
            },
            "movie.release_date.YYYY": {
                $gte: 2010
            },
            "popularity.rating": {
                $gte: 7
            }
        }
    },
    {
        $group: {
            _id: "$movie.origin_country",
            movieCount: {
                $sum: 1
            },
            movieTitle: {
                $push: "$movie.title"
            }
        }
    },
    { 
        $sort: {
            movieCount: -1
        }
    },
    {
        $limit: 3
    }
]);


// 2nd
db.fact_recommendation.aggregate([
    {
        $group: {
            _id: "$user.country",
            totalViewTime: {
                $sum: "$user.total_movie_viewtime"
            },
            numberOfUsers: {
                $sum: 1
            }
        }
    },
    {
        $sort: {
            numberOfUsers: -1
        }
    },
    {
        $limit: 3
    }
]);


// 3rd
db.fact_subscription.aggregate([
    {
        $match: {
            "isPaymentDue": true
        }
    },
    {
        $group: {
            _id: "$payment_detials.payment_method",
            totalDue: {
                $sum: "$total_payment_due"
            }
        }
    },
    {
        $sort: {
            totalDue: -1
        }
    },
    {
        $limit: 5
    }
]);

// 4th
db.fact_movie_insight.aggregate([
    {
        $match: {
            "financial_performance.revenue" : {
                $gte : 100000000
            },
            "popularity.rating": {
                $gte: 7
            }
        }
    },
    {
        $bucket: {
            groupBy: "$movie.release_date.YYYY",
            boundaries: [
                1950, 1960, 1970, 1980, 1990, 2000, 2010, 2020, 2030
            ],
            default: "Other",
            output: {
                "numberOfMovies": {
                    $sum: 1
                },
            }
        }
    },
    { 
        $sort: {
            "_id": -1
        }
    }
]);

// 5th
db.fact_movie_insight.aggregate([
    {
        $match: {
            "movie.genre": {
                $in: [
                    "Action",
                    "Adventure",
                    "Crime"
                ]
            }
        }
    },
    {
        $group: {
            _id: "$movie.genre",
            totalRevenue: {
                $avg: "$financial_performance.revenue"
            }
        }
    },
    { 
        $sort: {
            "totalRevenue": -1
        }
    }
]);