bobosales-analytics.sql

-- Signups by date and site
SELECT
    DATE_TRUNC('week', created_at)::date AS "date",
    COUNT(CASE WHEN site = 'bobosales.com' THEN 1 END) AS "Bobosales",
    COUNT(CASE WHEN site = 'shops.ksl.com' THEN 1 END) AS "Shops",
    COUNT(CASE WHEN site is null THEN 1 END) AS "Uncategorized"
FROM users
GROUP BY 1
ORDER BY 1 DESC
LIMIT 1;


-- Number of new rooms created
SELECT
    DATE_TRUNC('week', created_at)::date AS "date",
    COUNT(CASE WHEN site = 'bobosales.com' THEN 1 END) AS "Bobosales",
    COUNT(CASE WHEN site = 'shops.ksl.com' THEN 1 END) AS "Shops",
    COUNT(CASE WHEN site is null THEN 1 END) AS "Uncategorized"
FROM rooms
WHERE rooms.finished = 1
GROUP BY 1
ORDER BY 1 DESC
LIMIT 1;


-- Number of new tags created
SELECT
    DATE_TRUNC('week', t.created_at)::date AS "date",
    COUNT(CASE WHEN rooms.site = 'bobosales.com' THEN 1 END) AS "Bobosales",
    COUNT(CASE WHEN rooms.site = 'shops.ksl.com' THEN 1 END) AS "Shops",
    COUNT(CASE WHEN rooms.site is null THEN 1 END) AS "Uncategorized"
FROM room_items AS t
LEFT JOIN rooms ON t.room_id = rooms.id
WHERE rooms.finished = 1
GROUP BY 1
ORDER BY 1 DESC
LIMIT 1;


-- Number of rooms closed (sold out)
SELECT
    DATE_TRUNC('week', created_at)::date AS "date",
    COUNT(CASE WHEN site = 'bobosales.com' THEN 1 END) AS "Bobosales",
    COUNT(CASE WHEN site = 'shops.ksl.com' THEN 1 END) AS "Shops",
    COUNT(CASE WHEN site is null THEN 1 END) AS "Uncategorized"
FROM rooms
WHERE rooms.items_count <= items_sold
GROUP BY 1
ORDER BY 1 DESC
LIMIT 1;


-- Number of room views
-- Number of item views
-- Get from Google Analytics for now. See custom dashboard.


-- Number of Vendors
SELECT
    COUNT(*) AS "Bobosales"
FROM
    (SELECT COUNT(*)
     FROM rooms
     WHERE finished = 1 AND site = 'bobosales.com'
     GROUP BY user_id) AS bobosales;

SELECT
    COUNT(*) AS "Shops"
FROM
    (SELECT COUNT(*)
     FROM rooms
     WHERE finished = 1 AND site = 'shops.ksl.com'
     GROUP BY user_id) AS shops;


-- Average number of rooms per vendor
SELECT AVG(room.count) AS "Bobosales.com"
FROM
    (SELECT COUNT(id) AS "count"
     FROM rooms
     WHERE rooms.finished = 1 AND rooms.site = 'bobosales.com'
     GROUP BY user_id) AS room;

SELECT AVG(room.count) AS "Shops"
FROM
    (SELECT COUNT(id) AS "count"
     FROM rooms
     WHERE rooms.finished = 1 AND rooms.site = 'shops.ksl.com'
     GROUP BY user_id) AS room;


-- Average number of items per vendor
SELECT AVG(room.count) AS "Bobosales.com"
FROM
    (SELECT COUNT(items.id) AS count
     FROM room_items AS items
     INNER JOIN rooms ON rooms.id = items.room_id
     WHERE rooms.site = 'bobosales.com'
     GROUP BY rooms.user_id) AS room;

SELECT AVG(room.count) AS "Shops"
FROM
    (SELECT COUNT(items.id) AS count
     FROM room_items AS items
     INNER JOIN rooms ON rooms.id = items.room_id
     WHERE rooms.site = 'shops.ksl.com'
     GROUP BY rooms.user_id) AS room;


-- Average tags per room
SELECT AVG(room.item_count) AS "Bobosales"
FROM (SELECT COUNT(items) AS "item_count"
      FROM room_items AS items
      LEFT JOIN rooms ON items.room_id = rooms.id
      WHERE rooms.finished = 1 AND rooms.site = 'bobosales.com'
      GROUP BY room_id) AS "room";

SELECT AVG(room.item_count) AS "Shops"
FROM (SELECT COUNT(items) AS "item_count"
      FROM room_items AS items
      LEFT JOIN rooms ON items.room_id = rooms.id
      WHERE rooms.finished = 1 AND rooms.site = 'shops.ksl.com'
      GROUP BY room_id) AS "room";


-- Average dollar amount per vendor

-- These are very high because sellers indicate a high number
-- available
SELECT AVG(room.val) AS "Bobosales.com"
FROM
    (SELECT SUM(items.price * items.quantity) AS val
     FROM room_items AS items
     INNER JOIN rooms ON rooms.id = items.room_id
     WHERE rooms.site = 'bobosales.com'
     GROUP BY rooms.user_id) AS room;

SELECT AVG(room.val) AS "Shops"
FROM
    (SELECT SUM(items.price * items.quantity) AS val
     FROM room_items AS items
     INNER JOIN rooms ON rooms.id = items.room_id
     WHERE rooms.site = 'shops.ksl.com'
     GROUP BY rooms.user_id) AS room;


-- Total number tags
SELECT
    COUNT(CASE WHEN rooms.site = 'bobosales.com' THEN 1 END) AS "Bobosales",
    COUNT(CASE WHEN rooms.site = 'shops.ksl.com' THEN 1 END) AS "Shops",
    COUNT(CASE WHEN rooms.site is null THEN 1 END) AS "Uncategorized"
FROM room_items AS t
LEFT JOIN rooms ON t.room_id = rooms.id
WHERE rooms.finished = 1;


-- Total number of rooms
SELECT
    COUNT(CASE WHEN site = 'bobosales.com' THEN 1 END) AS "Bobosales",
    COUNT(CASE WHEN site = 'shops.ksl.com' THEN 1 END) AS "Shops",
    COUNT(CASE WHEN site is null THEN 1 END) AS "Uncategorized"
FROM rooms
WHERE rooms.finished = 1;


-- Total dollar amount of items
SELECT SUM(room.val) AS "Bobosales.com"
FROM
    (SELECT SUM(items.price * items.quantity) AS val
     FROM room_items AS items
     INNER JOIN rooms ON rooms.id = items.room_id
     WHERE rooms.site = 'bobosales.com'
     GROUP BY rooms.user_id) AS room;

SELECT SUM(room.val) AS "Shops"
FROM
    (SELECT SUM(items.price * items.quantity) AS val
     FROM room_items AS items
     INNER JOIN rooms ON rooms.id = items.room_id
     WHERE rooms.site = 'shops.ksl.com'
     GROUP BY rooms.user_id) AS room;


-- Total dollar amount of items per user
-- Use values from other queries and manually calculate it.
SELECT
    COUNT(CASE WHEN site = 'bobosales.com' THEN 1 END) AS "Bobosales",
    COUNT(CASE WHEN site = 'shops.ksl.com' THEN 1 END) AS "Shops",
    COUNT(CASE WHEN site is null THEN 1 END) AS "Uncategorized"
FROM users;