FREE AI-Enhanced Online SQLite Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE ItemTypes (
    Id INTEGER PRIMARY KEY,
    ItemShortDescription TEXT
);

CREATE INDEX idx_ItemTypes_ItemShortDescription ON ItemTypes (ItemShortDescription);

INSERT INTO ItemTypes (Id, ItemShortDescription) VALUES (1, 'Cost');
INSERT INTO ItemTypes (Id, ItemShortDescription) VALUES (2, 'Revenue');
INSERT INTO ItemTypes (Id, ItemShortDescription) VALUES (3, 'SomeOtherFinancialMetric1');
INSERT INTO ItemTypes (Id, ItemShortDescription) VALUES (4, 'SomeOtherFinancialMetric2');

CREATE TABLE ItemValues (
    CompanyId TEXT,
    ItemTypeId INTEGER,
    NumericValue INTEGER,
    DateEpoch INTEGER
);

CREATE INDEX idx_ItemValues_CompanyId ON ItemValues (CompanyId);
CREATE INDEX idx_ItemValues_ItemTypeId ON ItemValues (ItemTypeId);
CREATE INDEX idx_ItemValues_NumericValue ON ItemValues (NumericValue);
CREATE INDEX idx_ItemValues_DateEpoch ON ItemValues (DateEpoch);

INSERT INTO ItemValues (CompanyId, ItemTypeId, NumericValue, DateEpoch) VALUES ('AB1234', 1, 100, 1569884400);
INSERT INTO ItemValues (CompanyId, ItemTypeId, NumericValue, DateEpoch) VALUES ('AB1234', 2, 200, 1569884400);
INSERT INTO ItemValues (CompanyId, ItemTypeId, NumericValue, DateEpoch) VALUES ('G17895', 7, 50, 1632956400);

WITH salesIdsCTE AS (
    SELECT Id 
    FROM ItemTypes 
    WHERE ItemShortDescription = 'Cost' Or ItemShortDescription = 'Revenue'
),
filteredReportItems AS (
    SELECT * 
    FROM ItemValues 
    WHERE ItemTypeId IN (SELECT Id FROM salesIdsCTE)
    AND NumericValue > 5
)
SELECT *
FROM filteredReportItems
LIMIT 5;

CompanyId	ItemTypeId	NumericValue	DateEpoch
AB1234	1	100	1569884400
AB1234	2	200	1569884400