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 items posted ("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 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.item_count <= items_sold
GROUP BY 1
ORDER BY 1 DESC
LIMIT 1;

-- Number of Sellers

-- This doesn't work yet

SELECT
    COUNT(CASE WHEN users.site = 'bobosales.com' THEN 1 END) AS "Bobosales",
    COUNT(CASE WHEN users.site = 'shops.ksl.com' THEN 1 END) AS "Shops",
    COUNT(CASE WHEN users.site is null THEN 1 END) AS "Uncategorized"
FROM users
LEFT JOIN rooms ON rooms.user_id = users.id
GROUP BY users.id
HAVING COUNT(rooms.finished) > 0;

-- Average number of items per vendor
SELECT ( (SELECT COUNT(*)
          FROM room_items AS t
          LEFT JOIN rooms ON t.room_id = rooms.id
          WHERE rooms.finished = 1)
         /
         (SELECT COUNT(*)
          FROM users
          LEFT JOIN rooms ON rooms.user_id = users.id
          WHERE rooms.finished = 1
          HAVING COUNT(rooms.id) > 0)
       ) AS "Avg per Vendor";


-- Dollar amount per vender

-- Total number of rooms created
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 number of items posted ("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