Singlestore Now 2024 Raffle

!pip install pymongo pandas ipywidgets --quiet

shared_tier_check = %sql show variables like 'is_shared_tier'

if shared_tier_check and shared_tier_check[0][1] == 'ON':
    current_database = %sql SELECT DATABASE() as CurrentDatabase
    database_to_use = current_database[0][0]
else:
    database_to_use = "new_transactions"
    %sql CREATE DATABASE {{database_to_use}}

import os
import time
import numpy as np
import pandas as pd
import pymongo
from pymongo import MongoClient

# No need to edit anything
myclientmongodb = pymongo.MongoClient("mongodb+srv://mongo_sample_reader:SingleStoreRocks27017@cluster1.tfutgo0.mongodb.net/?retryWrites=true&w=majority")
mydbmongodb = myclientmongodb["new_transactions"]
mongoitems = mydbmongodb["items"]
mongocusts = mydbmongodb["custs"]
mongotxs = mydbmongodb["txs"]

db_to_use = database_to_use
s2clientmongodb = pymongo.MongoClient(connection_url_kai)
s2dbmongodb = s2clientmongodb[db_to_use]
s2mongoitems = s2dbmongodb["items"]
s2mongocusts = s2dbmongodb["custs"]
s2mongotxs = s2dbmongodb["txs"]

mongocollections = [mongoitems, mongocusts, mongotxs]

for mongo_collection in mongocollections:
    df = pd.DataFrame(list(mongo_collection.find())).reset_index(drop=True)
    data_dict = df.to_dict(orient='records')
    s2mongo_collection = s2dbmongodb[mongo_collection.name]
    s2mongo_collection.insert_many(data_dict)

num_iterations = 10
mongo_times = []

# Updated pipeline for total quantity of products sold across all products
pipeline = [
    {"$group": {"_id": None, "totalQuantity": {"$sum": "$item.quantity"}}}
]

# Simulating same for s2mongoitems
s2_times = []
for i in range(num_iterations):
    s2_start_time = time.time()
    s2_result = s2mongoitems.aggregate(pipeline)
    s2_stop_time = time.time()
    s2_times.append(s2_stop_time - s2_start_time)

# Retrieving total quantity from the result
total_quantity = next(s2_result)["totalQuantity"] if s2_result else 0

# Returning the numeric values of total quantity sold
print("Total Product Quantity Sold is",total_quantity)

# Updated pipeline to return the #1 selling product based on total quantity sold
pipeline = [
    {"$group": {
        "_id": "$item.name",  # Group by product name
        "total_quantity_sold": {"$sum": "$item.quantity"}  # Sum of quantities sold
    }},
    {"$sort": {"total_quantity_sold": -1}},  # Sort by total quantity sold in descending order
    {"$limit": 1}  # Limit to the top product
]

s2_result = s2mongoitems.aggregate(pipeline)

# Retrieve the name of the #1 selling product
top_product = next(s2_result, None)
if top_product:
    product_name = top_product["_id"]
    total_quantity_sold = top_product["total_quantity_sold"]
else:
    product_name = "No Data"
    total_quantity_sold = 0

# Return the #1 selling product and its total quantity sold
print("Top-Selling product : ",product_name,"With total quantity sold ",total_quantity_sold)

# Updated pipeline to exclude "Online" and get top-selling location
pipeline = [
    {"$lookup":
         {
          "from": "custs",
          "localField": "customer.email",
          "foreignField": "email",
          "as": "transaction_links",
         }
    },
    {"$match": {"store_location": {"$ne": "Online"}}},  # Exclude Online location
    {"$limit": 100},
    {"$group":
        {
         "_id": {"location": "$store_location"},
         "count": {"$sum": 1}
        }
    },
    {"$sort": {"count": -1}},
    {"$limit": 1}
]


s2_result = s2mongotxs.aggregate(pipeline)


# Retrieve the top-selling location excluding "Online"
top_location = next(s2_result, None)
if top_location:
    location_name = top_location["_id"]["location"]
    transaction_count = top_location["count"]
else:
    location_name = "No Data"
    transaction_count = 0

# Return the top-selling location and transaction count

print("Top-Selling Location : ",location_name,"With transaction of Count ",transaction_count)

shared_tier_check = %sql show variables like 'is_shared_tier'
if not shared_tier_check or shared_tier_check[0][1] == 'OFF':
    %sql DROP DATABASE IF EXISTS new_transactions;