[ANALYST] ANALYST QUERY TINKER SQL - fourslickz/notes GitHub Wiki

USER GROWTH

use Carbon\Carbon;
$total = Profile::where('created_at', '>=', Carbon::now()->subDays(7))->count();

DAU

SELECT COUNT(DISTINCT firebaseUID) AS total_unique_firebaseUID FROM log_user_login WHERE firebaseUID IS NOT NULL   AND created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY);

DAU WEEK

SELECT 
    DATE(created_at) AS tanggal,
    COUNT(DISTINCT firebaseUID) AS total_unique_firebaseUID
FROM log_user_login
WHERE firebaseUID IS NOT NULL
  AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DATE(created_at)
ORDER BY tanggal DESC;

MAU

SELECT COUNT(DISTINCT firebaseUID) AS total_unique_firebaseUID FROM log_user_login WHERE firebaseUID IS NOT NULL   AND created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH);

LAST WEEK

SELECT 
    DATE(created_at) AS tanggal,
    COUNT(DISTINCT firebaseUID) AS total_unique_firebaseUID
FROM log_user_login
WHERE firebaseUID IS NOT NULL
  AND created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY DATE(created_at)
ORDER BY tanggal DESC;

IN A MONTH

SELECT 
    DATE(created_at) AS tanggal,
    COUNT(DISTINCT firebaseUID) AS total_unique_firebaseUID
FROM log_user_login
WHERE firebaseUID IS NOT NULL
  AND created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
GROUP BY DATE(created_at)
ORDER BY tanggal ASC;

QUERY GROWTH USER BETWEEN DATE

$data = Profile::raw(function ($collection) {
    return $collection->aggregate([
        [
            '$match' => [
                'created_at' => [
                    '$gte' => new \MongoDB\BSON\UTCDateTime(strtotime('2026-02-01 00:00:00 UTC') * 1000),
                    '$lt'  => new \MongoDB\BSON\UTCDateTime(strtotime('2026-02-13 00:00:00 UTC') * 1000),
                ]
            ]
        ],
        [
            '$group' => [
                '_id' => [
                    '$dateToString' => [
                        'format' => '%Y-%m-%d',
                        'date' => '$created_at',
                        'timezone' => 'Asia/Jakarta'
                    ]
                ],
                'total' => ['$sum' => 1]
            ]
        ],
        [
            '$project' => [
                '_id' => 0,
                'tanggal' => '$_id',
                'total' => 1
            ]
        ],
        [
            '$sort' => ['tanggal' => 1]
        ]
    ]);
});

FIND NEW MEMBER FROM KWARDA & KWARCAB

$data = Profile::raw(function ($collection) {
    return $collection->aggregate([
        [
            '$match' => [
                'kwarda' => 'JAWA TIMUR',
                'kwarcab' => [
                    '$regex' => 'KAB. BOJONEGORO',
                    '$options' => 'i'
                ],
                'created_at' => [
                    '$gte' => new \MongoDB\BSON\UTCDateTime(
                        strtotime('2026-02-17 00:00:00 UTC') * 1000
                    )
                ]
            ]
        ],
        [
            '$count' => 'total'
        ]
    ]);
});

$data = Profile::raw(function ($collection) {
    return $collection->aggregate([
        [
            '$match' => [
                'kwarda' => 'JAWA TIMUR',
                'kwarcab' => [
                    '$regex' => 'KAB. BOJONEGORO',
                    '$options' => 'i'
                ],
            ]
        ],
        [
            '$count' => 'total'
        ]
    ]);
});

MULTI KWARCAB

$date = new \MongoDB\BSON\UTCDateTime(
    strtotime('2026-03-01 00:00:00 UTC') * 1000
);

$kwarcabList = [
    'BANYUWANGI',
    'MADIUN',
    'GRESIK',
    'PONOROGO',
    'KOTA SURABAYA',
    'KAB. SIDOARJO',
    'KAB. BOJONEGORO'
];

$data = Profile::raw(function ($collection) use ($date, $kwarcabList) {
    return $collection->aggregate([
        [
            '$match' => [
                'kwarda' => 'JAWA TIMUR',
                'kwarcab' => [
                    '$in' => $kwarcabList
                ],
                'created_at' => [
                    '$gte' => $date
                ]
            ]
        ],
        [
            '$group' => [
                '_id' => '$kwarcab',
                'total' => ['$sum' => 1]
            ]
        ],
        [
            '$project' => [
                '_id' => 0,
                'kwarcab' => '$_id',
                'total' => 1
            ]
        ],
        [
            '$sort' => [
                'kwarcab' => 1
            ]
        ]
    ]);
});

GROUP GOL PRAMUKA

$data = Profile::raw(function ($collection) use ($date, $kwarcabList) {
    return $collection->aggregate([
        [
            '$match' => [
                'kwarda' => 'JAWA TENGAH',
                'kwarcab' => [
                    '$in' => $kwarcabList
                ],
                'created_at' => [
                    '$gte' => $date
                ]
            ]
        ],
        [
            '$group' => [
                '_id' => [
                    'kwarcab' => '$kwarcab',
                    'golPramuka' => '$golPramuka'
                ],
                'total' => ['$sum' => 1]
            ]
        ],
        [
            '$group' => [
                '_id' => '$_id.kwarcab',
                'golPramuka' => [
                    '$push' => [
                        'golPramuka' => '$_id.golPramuka',
                        'total' => '$total'
                    ]
                ],
                'total' => [
                    '$sum' => '$total'
                ]
            ]
        ],
        [
            '$project' => [
                '_id' => 0,
                'kwarcab' => '$_id',
                'total' => 1,
                'golPramuka' => 1
            ]
        ],
        [
            '$sort' => [
                'kwarcab' => 1
            ]
        ]
    ]);
});

FILTERED

$startDate = new \MongoDB\BSON\UTCDateTime(
    strtotime('2026-03-01 00:00:00 UTC') * 1000
);

$endDate = new \MongoDB\BSON\UTCDateTime(
    strtotime('2026-03-31 00:00:00 UTC') * 1000
);

$kwarcabList = [
'KAB. LEBAK',
'KOTA TASIKMALAYA',
'KAB. BEKASI',
'KAB. TANGERANG',
'KOTA JAKARTA TIMUR',
'KAB. SERANG',
'KAB. MAJALENGKA',
'KAB. CIREBON',
'KAB. CIANJUR',
'KAB. BANDUNG BARAT',
'KAB. BANDUNG',
'KAB. KARAWANG',
'KOTA JAKARTA BARAT'
];

$data = Profile::raw(function ($collection) use ($startDate, $endDate, $kwarcabList) {
    return $collection->aggregate([
        [
            '$match' => [
                'kwarda' => 'JAWA BARAT',
                'kwarcab' => [
                    '$in' => $kwarcabList
                ],
                'created_at' => [
                    '$gte' => $startDate,
                    '$lte' => $endDate
                ]
            ]
        ],
        [
            '$group' => [
                '_id' => '$kwarcab',

                'total' => ['$sum' => 1],

                'siaga' => [
                    '$sum' => [
                        '$cond' => [
                            ['$eq' => ['$golPramuka', 'Siaga']],
                            1,
                            0
                        ]
                    ]
                ],

                'penggalang' => [
                    '$sum' => [
                        '$cond' => [
                            ['$eq' => ['$golPramuka', 'Penggalang']],
                            1,
                            0
                        ]
                    ]
                ],

                'penegak' => [
                    '$sum' => [
                        '$cond' => [
                            ['$eq' => ['$golPramuka', 'Penegak']],
                            1,
                            0
                        ]
                    ]
                ],

                'pandega' => [
                    '$sum' => [
                        '$cond' => [
                            ['$eq' => ['$golPramuka', 'Pandega']],
                            1,
                            0
                        ]
                    ]
                ],

                'pembina' => [
                    '$sum' => [
                        '$cond' => [
                            [
                                '$not' => [
                                    '$in' => [
                                        '$golPramuka',
                                        ['Siaga', 'Penggalang', 'Penegak', 'Pandega']
                                    ]
                                ]
                            ],
                            1,
                            0
                        ]
                    ]
                ],
            ]
        ],
        [
            '$project' => [
                '_id' => 0,
                'kwarcab' => '$_id',
                'total' => 1,
                'siaga' => 1,
                'penggalang' => 1,
                'penegak' => 1,
                'pandega' => 1,
                'pembina' => 1,
            ]
        ],
        [
            '$sort' => [
                'kwarcab' => 1
            ]
        ]
    ]);
});