Skip to content

Instantly share code, notes, and snippets.

View insert_data.py
def insert_data():
"""insert data into a collection"""
doc = [{
"id":"t1", "price": "$125.00 ", "latitude": "37.78097", "longitude": "-122.40305"
}]
rs.Collection.add_docs(collection_name, doc)
View field_mappings_snippet.py
rs = Client(api_server='api.rs2.usw2.rockset.com', api_key='APIKEY')
mappings = [
rs.FieldMapping.mapping(
name="twitchTransformation1",
input_fields=[
rs.FieldMapping.input_field(
field_name="price",
if_missing="SKIP",
is_drop=True,
View createTable.sql
CREATE TABLE sfairbnb
(id INT(10) UNSIGNED PRIMARY KEY,
listing_url TEXT,
scrape_id TEXT,
last_scraped VARCHAR(10),
name VARCHAR(20),
summary TEXT,
space TEXT,
description TEXT,
experiences_offered TEXT,
@nfarah86
nfarah86 / movie.json
Created Apr 14, 2021
Sample movie data that comes from mongodb
View movie.json
[
{
"original_title": "Delusion",
"belongs_to_collection": "",
"homepage": "",
"_event_time": "2021-04-01T18:34:08.119000Z",
"overview": "In this fast-paced, noirish road movie, a computer expert embezzles half a million dollars and races off to Reno to start anew. Unfortunately, en route, he picks up a pair of hitchers and ends up entangled with a crazed couple who commandeer his car and leave him alone in the desert to die. As soon as he can, he hits the road to get revenge and to find his money before they do.",
"poster_path": "/g2o1J0ulttuwovqLc0ho910MR95.jpg",
"status": "Released",
"vote_average": 4.8,
@nfarah86
nfarah86 / percentageOfSalesCaprisAndTop.sql
Created Feb 24, 2021
Percentage of sales of customers who bought capris tights and a top
View percentageOfSalesCaprisAndTop.sql
with hits as (
SELECT
count(*) totalTopAndCaprisPurchased
FROM
commons.customerActivity ca
WHERE
length(
ARRAY_INTERSECT(
ca.AddedToShoppingCart,
ARRAY_CREATE('pkrcapris', 'pkrtop')
@nfarah86
nfarah86 / promoCodesUsedInRegions.sql
Last active Feb 24, 2021
Promo codes being used in certain regions of the U.S.
View promoCodesUsedInRegions.sql
SELECT
oi.PromoID,
pc.PromoDescription,
ca.CustomerRegion,
COUNT(*) as totalPromoUsed
FROM
commons.orderItem oi
join commons.customerActivity ca on ca.CustomerID = oi.CustomerID
Join commons.promoCode pc on pc.PromoID = oi.PromoID
WHERE
@nfarah86
nfarah86 / totalRevenue.sql
Created Feb 24, 2021
Get the sum of all orders
View totalRevenue.sql
select
SUM(
CAST(
REGEXP_REPLACE(o.OrderTotal, '[$,\,]', '') AS float
)
) as totalRevenue
from
commons.OrderItem o
;
@nfarah86
nfarah86 / customersWhoDidNotPurchase.sql
Created Feb 24, 2021
Customers who added items to their shopping cart and did not purchase
View customersWhoDidNotPurchase.sql
-- Note: peak time is 14; we hardcorded because of the way the sample data is. Normally we would use current timestamp.
SELECT
ca.CustomerID, ca.CustomerEmail, ca._event_time
FROM
commons.CustomerActivity ca
WHERE
NOT EXISTS (
SELECT
oi.CustomerID
View totalPurchasesHourAndMinute.sql
-- gets the total purcases by hour and minute
SELECT
EXTRACT(
HOUR
FROM
CAST(o.OrderDate as timestamp)
) as hour,
EXTRACT(
MINUTE
@nfarah86
nfarah86 / find_all_customers_with_parameters.sql
Created Dec 14, 2020
Finds customer's history via parameters for email and full name
View find_all_customers_with_parameters.sql
Select
full_name,
created_at,
active,
email,
last_updated,
customer_id
from
customer_orders
WHERE