Tidyquery SQL queries for dataframes inside R programming

dplyr mutate to create column based on str_detect.md

#Extract store names in dplyr via mutate

data_store<- data%>%
mutate(Store=
ifelse(str_detect(OrderId, "US"),"US",
ifelse(str_detect(OrderId, "CA"),"CA",
ifelse(str_detect(OrderId, "UK"),"UK",
"Other"))))
View(data_store)

total trans and qty WHERE Category condition and Date condition met.md

#Total transactions + Qty_sold WHERE Category1 AND Dates
#Within 1/1/2021 and 1/7/2021

query("SELECT
COUNT_DISTINCT (OrderId) AS total_trans,
SUM (Qty_sold) AS total_Qty FROM
data
WHERE Category = 'Category1' AND
(Date BETWEEN TO_DATE('2021-1-1') AND TO_DATE('2021-1-7'))")

total_trans and qty where category criteria met.md

#Total transactions + Qty_sold WHERE Category1

query("SELECT
COUNT_DISTINCT (OrderId) AS total_trans,
SUM (Qty_sold) AS total_Qty FROM
data
WHERE Category = 'Category1'")

total_transactions and qty.md

#Total transactions + Qty_sold

query("SELECT COUNT_DISTINCT (OrderId) AS total_trans,
SUM (Qty_sold) AS total_Qty FROM data")

total_transactions.md

#Total transactions in dataset

query("SELECT COUNT_DISTINCT (OrderId) AS transactions FROM data")

use dplr mutate and tidyquery in same query.md

##Use mutate / dplyr in same function as SQL
data%>%
mutate(Store=
ifelse(str_detect(OrderId, "US"),"US",
ifelse(str_detect(OrderId, "CA"),"CA",
ifelse(str_detect(OrderId, "UK"),"UK",
"Other"))))%>%
query("SELECT
Store,
COUNT_DISTINCT (OrderId) AS total_trans,
SUM (Qty_sold) AS total_Qty
WHERE Category = 'Category1' AND
(Date BETWEEN TO_DATE('2021-1-1') AND TO_DATE('2021-1-7'))
GROUP BY Store")