Website: Market

CREATE VIEW dbo.WEB_MarketListings
AS
SELECT c.Name                                              AS Seller,
       x.r                                                 AS [Row],
       SUBSTRING(c.MarketStore, x.P, 16)                   AS ItemData,
       CAST(SUBSTRING(c.MarketStore, x.P + 9, 1) AS int) / 16                    AS Section,
       CAST(SUBSTRING(c.MarketStore, x.P, 1) AS int)
         + (CAST(SUBSTRING(c.MarketStore, x.P + 7, 1) AS int) & 128) * 2          AS Number,
       (CAST(SUBSTRING(c.MarketStore, x.P + 1, 1) AS int) / 8) & 15               AS [Level],
       CAST(SUBSTRING(c.MarketStore, x.P + 7, 1) AS int) & 63                     AS Excellent,
       CAST(SUBSTRING(c.MarketStore, x.P + 16, 1) AS int)                         AS Currency,
       CAST(CAST(SUBSTRING(c.MarketStore, x.P + 24, 1) + SUBSTRING(c.MarketStore, x.P + 23, 1)
               + SUBSTRING(c.MarketStore, x.P + 22, 1) + SUBSTRING(c.MarketStore, x.P + 21, 1)
               + SUBSTRING(c.MarketStore, x.P + 20, 1) + SUBSTRING(c.MarketStore, x.P + 19, 1)
               + SUBSTRING(c.MarketStore, x.P + 18, 1) + SUBSTRING(c.MarketStore, x.P + 17, 1) AS binary(8)) AS bigint) AS Price,
       DATEADD(second,
               CAST(CAST(SUBSTRING(c.MarketStore, x.P + 28, 1) + SUBSTRING(c.MarketStore, x.P + 27, 1)
                       + SUBSTRING(c.MarketStore, x.P + 26, 1) + SUBSTRING(c.MarketStore, x.P + 25, 1) AS binary(4)) AS bigint),
               '19700101')                                 AS ListedAtUtc
FROM dbo.Character c
CROSS APPLY (SELECT TOP (50) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS r FROM sys.all_objects) n
CROSS APPLY (SELECT n.r AS r, 22 + n.r * 29 AS P) x          -- SQL is 1-based: row r starts at byte 22 + r*29
WHERE c.MarketStore IS NOT NULL
  AND SUBSTRING(c.MarketStore, 1, 1) = 0x01                                    -- version 1
  AND SUBSTRING(c.MarketStore, x.P + 16, 1) BETWEEN 0x01 AND 0x07              -- listed
GO

SELECT Seller, [Row], ItemData, Section, Number, [Level], Excellent, Currency, Price, ListedAtUtc
FROM dbo.WEB_MarketListings
ORDER BY ListedAtUtc DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

SELECT Seller, ItemData, [Level], Price
FROM dbo.WEB_MarketListings
WHERE Currency = 1 AND [Level] >= 9 AND Excellent <> 0
ORDER BY Price ASC;

SELECT Seller, ItemData, [Level], Currency, Price
FROM dbo.WEB_MarketListings
WHERE Section = 0 AND Number = 19
ORDER BY Currency, Price;

SELECT [Row], ItemData, Currency, Price, ListedAtUtc
FROM dbo.WEB_MarketListings
WHERE Seller = @name
ORDER BY [Row];

SELECT Currency, COUNT(*) AS Listings
FROM dbo.WEB_MarketListings
GROUP BY Currency;

CREATE NONCLUSTERED INDEX IX_MarketSaleLog_SoldAt ON dbo.MarketSaleLog (SoldAt DESC) INCLUDE (Status, Seller, Buyer, Currency, Price);

SELECT TOP 20 Id, SoldAt, Seller, Buyer, ItemData, Currency, Price
FROM dbo.MarketSaleLog
WHERE Status = 1
ORDER BY Id DESC;

SELECT TOP 50 SoldAt, Buyer, ItemData, Currency, Price, Net
FROM dbo.MarketSaleLog
WHERE Seller = @name AND Status = 1
ORDER BY Id DESC;

SELECT TOP 50 SoldAt, Seller, ItemData, Currency, Price
FROM dbo.MarketSaleLog
WHERE Buyer = @name AND Status = 1
ORDER BY Id DESC;

SELECT CAST(SoldAt AS date) AS [Day],
       COUNT(*)   AS Sales,
       MIN(Price) AS Lowest,
       AVG(Price) AS Average
FROM dbo.MarketSaleLog
WHERE Status = 1 AND Currency = 1
  AND SoldAt >= DATEADD(day, -30, GETDATE())
  AND CAST(SUBSTRING(ItemData, 10, 1) AS int) / 16 = @section
  AND CAST(SUBSTRING(ItemData, 1, 1) AS int) + (CAST(SUBSTRING(ItemData, 8, 1) AS int) & 128) * 2 = @number
GROUP BY CAST(SoldAt AS date)
ORDER BY [Day];

CREATE TABLE dbo.WEB_MarketSales
(
    Id       bigint        NOT NULL PRIMARY KEY,   -- same Id as MarketSaleLog
    Seller   varchar(10)   NOT NULL,
    Buyer    varchar(10)   NOT NULL,
    ItemData varbinary(16) NOT NULL,
    Currency tinyint       NOT NULL,
    Price    bigint        NOT NULL,
    Net      bigint        NOT NULL,
    SoldAt   datetime      NOT NULL
)
CREATE NONCLUSTERED INDEX IX_WEB_MarketSales_Seller ON dbo.WEB_MarketSales (Seller, Currency);
CREATE NONCLUSTERED INDEX IX_WEB_MarketSales_SoldAt ON dbo.WEB_MarketSales (SoldAt DESC);
GO
CREATE PROCEDURE dbo.WEB_MarketSalesUpdate
AS
BEGIN
    SET NOCOUNT ON;

    -- Copies every finished sale not copied yet. A coin sale can turn from Status 0 to 1
    -- after newer rows were written, so this checks every row instead of "Id > last Id".
    -- MarketSaleLog stays small (max SaleLogKeep rows per seller), so this is cheap.
    INSERT INTO dbo.WEB_MarketSales (Id, Seller, Buyer, ItemData, Currency, Price, Net, SoldAt)
    SELECT s.Id, s.Seller, s.Buyer, s.ItemData, s.Currency, s.Price, s.Net, s.SoldAt
    FROM dbo.MarketSaleLog s
    WHERE s.Status = 1
      AND NOT EXISTS (SELECT 1 FROM dbo.WEB_MarketSales a WHERE a.Id = s.Id);
END
GO

EXEC dbo.WEB_MarketSalesUpdate;

SELECT TOP 100 a.Seller, COUNT(*) AS Sales, SUM(a.Net) AS ZenEarned
FROM dbo.WEB_MarketSales a
JOIN dbo.Character c ON c.Name = a.Seller          -- hides deleted characters
WHERE a.Currency = 1
GROUP BY a.Seller
ORDER BY ZenEarned DESC;

SELECT CAST(SoldAt AS date) AS [Day], Currency, COUNT(*) AS Sales, SUM(Price) AS Volume
FROM dbo.WEB_MarketSales
WHERE SoldAt >= DATEADD(day, -30, GETDATE())
GROUP BY CAST(SoldAt AS date), Currency
ORDER BY [Day], Currency;