Kueri frekuensi,nilai pesanan rata-rata dan jumlah total pengguna unik dengan status selesai dan dalam kelompok bulan.

nomor2 <- dbGetQuery(jengki,"
                   select o.user_id, count(oi.order_id) as total_order, avg(oi.sale_price) as average_expenses from orders as o
                   inner join order_items as oi on oi.order_id = o.order_id
                   inner join user as u on u.id=o.user_id
                   where oi.status
                   in ('Complete')
                   group by o.user_id
                   order by average_expenses desc
                   ")
datatable(nomor2)

## Warning in instance$preRenderHook(instance): It seems your data is too big for
## client-side DataTables. You may consider server-side processing:
## https://rstudio.github.io/DT/server.html

##Perbandingan Order cancel,complete & returned.

nomor3 <- dbGetQuery(jengki,"
        select * from orders as o
           where status
               in ('Complete','Cancelled','Returned')
                   ")
nomor3 %>%
  count (status)%>%
  arrange(desc(n))%>%
  hchart('bar', hcaes(x= 'status', y = 'n', color=c("red","green","yellow")))%>%
  hc_add_theme(hc_theme_google())%>%
  hc_tooltip(pointFormat= '<b>Total Pemesanan:</b> {point.y} <br>')%>%
  hc_title(text = 'Total Pembelian berdasarkan status',
           style = list(fontSize = '30px', fontWeight = 'bold'))%>%
  
            
  hc_credits(enabled = TRUE, text = 'jengkolbalado')

##Visualisasi data jumlah data customer berdasarkan negaranya.

nomor4 <- dbGetQuery(jengki,"
  select country, count(country) as frec_user from user
       group by country
       ")

nomor4<- nomor4%>%
mutate(pert= frec_user/sum(frec_user))


nomor4%>%
arrange(desc(frec_user))%>%
hchart("pie",hcaes(x=country, y=frec_user,color=viridis::mako(15)))%>%
  hc_add_theme(hc_theme_google())%>%
          hc_tooltip(pointFormat = '<b>User :</b> {point.y} <br>')%>%
 
  hc_title(text= 'customers based on country',
           style =list(fontSize = '35px', fontWeight = 'bold'))%>%
  hc_credits(enabled = TRUE, text = 'jengkolbalado')

nomor6 <- dbGetQuery(jengki,"
         select * from orders as o
         where status = 'Complete'")
nomor6 <- dbGetQuery(jengki,"
          select o.user_id,sum(oi.sale_price) as total_expenses,o.created_at from orders as o
          inner join order_items as oi on oi.order_id = o.order_id
          inner join user as u on u.id=o.user_id
          where oi.status
          in ('Complete')
          group by o.user_id
          ")

test1 <- separate(nomor6,col=created_at, into=c("date","time"),sep="")

## Warning: Expected 2 pieces. Additional pieces discarded in 27679 rows [1, 2, 3, 4, 5, 6,
## 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, ...].

test2 <- separate(test1, col=date,into=c("year","month","day"),sep="-")

## Warning: Expected 3 pieces. Missing pieces filled with `NA` in 27679 rows [1, 2, 3, 4,
## 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, ...].

#dbWriteTable(jengki,"nomor6",test2)
nmr6 <- dbGetQuery(jengki,"select year,month, sum(total_expenses) as total_expenses from nomor6 group by year, month")

plot_ly(nmr6,x=~year, y=~total_expenses, type="bar",color=~month)%>%
  layout(title="r",
         yaxis=list(title="rd"))

## Warning in RColorBrewer::brewer.pal(N, "Set2"): minimal value for n is 3, returning requested palette with 3 different levels

## Warning in RColorBrewer::brewer.pal(N, "Set2"): minimal value for n is 3, returning requested palette with 3 different levels

Negara dengan pelanggan terbanyak beserta Jenis kelamin dan umurnya serta Keuntungan Profit

nomor7 <- dbGetQuery(jengki,"
      select u.id,u.first_name,u.country,u.gender, u.age,sum(sale_price)-sum(i.cost)-sum(p.cost) as total_profit from orders as o
  inner join order_items as oi on o.order_id=oi.order_id
    inner join inventory_items as i on i.id = oi.inventory_item_id
  inner join user as u on u.id = o.user_id
  inner join products as p on p.id=oi.product_id
     group by u.age
    order by total_profit desc
      ")
datatable(nomor7)

nomor9cat <- dbGetQuery(jengki,"
  select p.category,sum(o.num_of_item) as sales, oi.sale_price as price_item,sum(oi.sale_price) as revenue from orders as o
  inner join order_items as oi on o.order_id=oi.order_id
  inner join products as p on p.id=oi.order_id
  where o.status ='Complete'
  group by p.category 
  order by revenue desc
  ")
datatable(nomor9cat)

nmr9 <- dbGetQuery(jengki, "select p.category,sum(o.num_of_item) as sales, sum(oi.sale_price) as revenue from orders as o inner join order_items as oi on o.order_id = oi.order_id inner join products as p on p.id=oi.order_id where o.status = 'Complete' group by p.category order by revenue")

nmr9.2 <- dbGetQuery(jengki, "select p.category, p.brand, sum(o.num_of_item) as sales , sum(oi.sale_price) as revenue from orders as o inner join order_items as oi on o.order_id = oi.order_id inner join products as p on p.id=oi.order_id where o.status = 'Complete' group by p.brand order by revenue desc limit 25")

# TOp sales
nomor9cat %>%
  hchart("pie", hcaes(x=category,y=sales, color = viridis::mako(26)))%>%
  hc_add_theme(hc_theme_google())