Comparing SQL views and joins for lag calculation

Data and Databases
1

I’m working on a SQL query for a product database. Each product has a name, timescale, and creation time. I need to calculate a ‘lag’ value based on the timescale and creation time. Here’s my current setup:

CREATE TABLE Items (
    ItemID INT PRIMARY KEY,
    ItemName TEXT,
    Scale INT,
    Created INT
);

INSERT INTO Items VALUES (1, 'Widget', 1, 150);
INSERT INTO Items VALUES (2, 'Gadget', 2, 2000);

SELECT ItemName, Scale, Created
FROM Items
WHERE (Scale = 1 AND Created > 100) OR (Scale = 2 AND Created > 1000);

The lag is calculated differently for each scale:

  • Scale 1: lag = Created - 100
  • Scale 2: lag = Created - 1000

I want to sort all items by their lag. Should I use a view to add a lag column or is there a better way with joins? Any suggestions on how to approach this?

2

hey leo, try a CASE statement in your query: SELECT ItemName, Scale, Created, CASE WHEN Scale=1 THEN Created-100 WHEN Scale=2 THEN Created-1000 END AS Lag FROM Items WHERE (Scale=1 AND Created>100) OR (Scale=2 AND Created>1000) ORDER BY Lag. no extra views or joins needed.

3

For your scenario, I’d recommend using a Common Table Expression (CTE) rather than a view or join. CTEs offer flexibility and readability, especially for complex calculations like your lag. Here’s how you could approach it:

WITH LaggedItems AS (
    SELECT 
        ItemName, 
        Scale, 
        Created,
        CASE 
            WHEN Scale = 1 THEN Created - 100
            WHEN Scale = 2 THEN Created - 1000
        END AS Lag
    FROM Items
    WHERE (Scale = 1 AND Created > 100) OR (Scale = 2 AND Created > 1000)
)
SELECT * FROM LaggedItems ORDER BY Lag;

This method calculates the lag inline, avoiding the need for a permanent view or complex joins. It’s efficient, easy to modify, and keeps your base table structure intact. Plus, it allows for easy sorting by lag as requested.

4

thats an interesting problem! have u considered using a subquery instead? it could be more flexible than a view. something like:

SELECT *,
CASE
WHEN Scale = 1 THEN Created - 100
WHEN Scale = 2 THEN Created - 1000
END AS Lag
FROM Items
WHERE (Scale = 1 AND Created > 100) OR (Scale = 2 AND Created > 1000)
ORDER BY Lag

what do you think? would that work for ur needs?