FREE AI-Enhanced Online SQLite Compiler

Untitled*

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

CREATE TABLE Features(
  Id    INTEGER PRIMARY KEY, 
  Featurename  TEXT
);
CREATE TABLE PriceRules(
  Id     INTEGER PRIMARY KEY, 
  Price  REAL   
);
CREATE TABLE FeaturesPriceRules(
  FeatureId   INTEGER, 
  PriceRuleId INTEGER,
  FOREIGN KEY(FeatureId) REFERENCES Features(Id),
  FOREIGN KEY(PriceRuleId) REFERENCES PriceRules(Id)
);

CREATE TABLE FeatureCombinations(
  PriceRuleId INTEGER,
  FeatureId   INTEGER,   
  FOREIGN KEY(PriceRuleId) REFERENCES PriceRules(Id),
  FOREIGN KEY(FeatureId) REFERENCES Features(Id)  
);

CREATE TABLE OrderPositions(
  Id INTEGER  PRIMARY KEY, 
  OrderId   INTEGER,   
  FeatureId   INTEGER,     
  FOREIGN KEY(FeatureId) REFERENCES Features(Id)  
);

INSERT INTO Features (Id, Featurename)
VALUES (1, 'F1'), (2, 'F2'), (3, 'F3'),
       (4, 'F6'), (5, 'F7'), (6, 'F9'),
       (7, 'E1'), (8, 'ZA2');

INSERT INTO PriceRules (Id, Price)
VALUES (101, '6.00'), (102, '4.00'), (103, '2.00'),
       (105, '24.00'), (106, '22.00');  
    
INSERT INTO FeaturesPriceRules (FeatureId, PriceRuleId)
VALUES (1, 101), (1, 102), (1, 103),(7, 105),(7, 106);  
               
INSERT INTO FeatureCombinations (PriceRuleId, FeatureId)
VALUES (101, 1), (102, 1), (102, 2),
       (103, 1), (103, 2),(103, 3), 
       (105, 7), (106, 7), (106, 8); 
       
INSERT INTO OrderPositions(Id, OrderId, FeatureId)
VALUES (211, 401, 1), (221, 402, 1),(222, 402, 2),
(231, 403, 1),(232, 403, 2),(233, 403, 3);         
   
select
  op.orderid,
  op.id as orderpositionid,
  op.featureid,
  f.featurename,
  (
     select pr.price
     from featurespricerules fpr
     join PriceRules pr on pr.id = fpr.PriceRuleId
     join featurecombinations fc on fc.priceruleid = pr.id
     left join OrderPositions op2 on op2.orderid = op.orderid
                                 and op2.featureid = fc.featureid
     where fpr.featureid = op.featureid
     group by pr.id
     having count(op2.id) = count(*)
     order by count(*) desc
     limit 1
  ) as price
from orderpositions op
join features f on f.id = op.featureid
WHERE price is NOT NULL
order by op.orderid, op.id;

OrderId	orderpositionid	FeatureId	Featurename	price
401	211	1	F1	6.0
402	221	1	F1	4.0
403	231	1	F1	2.0