Process EC2 price list csv with SQLite to get RI prices

EC2 RI prices from pricing API.sql

-- curl https://pricing.us-east-1.amazonaws.com/offers/v1.0/aws/AmazonEC2/current/index.csv | tail -n +6 > ec2.csv.no_head
-- sqlite> .mode csv
-- sqlite> .import ec2.csv.no_head ec2
-- sqlite> .save ec2.sqlite

WITH prices AS (
	WITH 
	loc AS (
		SELECT "Asia Pacific (Sydney)" AS Location, "ap-southeast-2" AS Region
		UNION SELECT "Asia Pacific (Singapore)", "ap-southeast-1"
		UNION SELECT "US East (N. Virginia)", "us-east-1"
		UNION SELECT "South America (Sao Paulo)", "sa-east-1"
		UNION SELECT "US West (Oregon)", "us-east-1"
		UNION SELECT "US West (N. California)", "us-west-1"
		UNION SELECT "EU (Ireland)", "eu-west-1"
	),
	os AS (
		SELECT "Operating System", "Pre Installed S/W" 
		FROM ec2 
		WHERE 
			"Operating System" != "RHEL" AND
			"Operating System" != "SUSE" AND
			"Operating System" != "NA" AND
			"Pre Installed S/W" != "SQL Server Enterprise" AND
			--NOT ("Operating System" = "Windows" AND "Pre Installed S/W" = "NA") AND
			"Operating System" != ""
		GROUP BY "Operating System", "Pre Installed S/W"
	)
	SELECT 
		Region,
		os."Operating System",
		os."Pre Installed S/W",
		"Instance Type",
		"TermType", 
		"PurchaseOption", 
		"LeaseContractLength", 
		"Unit",
		CAST("PricePerUnit" AS REAL) AS PricePerUnit,
		RateCode
	FROM ec2 
	JOIN loc ON (loc.Location = ec2.Location)
	JOIN os ON (os."Operating System" = ec2."Operating System" AND os."Pre Installed S/W" = ec2."Pre Installed S/W")
	WHERE 
		Currency = "USD" AND
		Tenancy = "Shared"
	GROUP BY
		Region,
		os."Operating System",
		os."Pre Installed S/W",
		"Instance Type",
		"TermType", 
		"PurchaseOption", 
		"LeaseContractLength", 
		"Unit",
		"PricePerUnit",
		RateCode
)
SELECT
	RateCode,
	"Region", 	
	"Operating System",
	"Pre Installed S/W",
	"Instance Type",
	printf("%s %s %s %s", Region, "Operating System", "Pre Installed S/W", "Instance Type") AS Lookup,
	MAX(CASE WHEN prices.TermType = 'OnDemand' AND prices.Unit = 'Hrs' THEN prices.PricePerUnit ELSE 0.0 END) AS "On Demand ($/h)",
	MAX(CASE WHEN prices.LeaseContractLength = '1yr' AND prices.Unit = 'Hrs' AND prices.PurchaseOption = 'All Upfront' THEN prices.PricePerUnit ELSE 0.0 END) AS "1yr All Upfront ($/h)",
	MAX(CASE WHEN prices.LeaseContractLength = '1yr' AND prices.Unit = 'Quantity' AND prices.PurchaseOption = 'All Upfront' THEN prices.PricePerUnit ELSE 0.0 END) AS "1yr All Upfornt ($)",
	MAX(CASE WHEN prices.LeaseContractLength = '1yr' AND prices.Unit = 'Hrs' AND prices.PurchaseOption = 'No Upfront' THEN prices.PricePerUnit ELSE 0.0 END) AS "1yr No Upfront ($/h)",
	MAX(CASE WHEN prices.LeaseContractLength = '1yr' AND prices.Unit = 'Quantity' AND prices.PurchaseOption = 'No Upfront' THEN prices.PricePerUnit ELSE 0.0 END) AS "1yr No Upfront ($)",
	MAX(CASE WHEN prices.LeaseContractLength = '1yr' AND prices.Unit = 'Hrs' AND prices.PurchaseOption = 'Partial Upfront' THEN prices.PricePerUnit ELSE 0.0 END) AS "1yr Partial Upfront ($/h)",
	MAX(CASE WHEN prices.LeaseContractLength = '1yr' AND prices.Unit = 'Quantity' AND prices.PurchaseOption = 'Partial Upfront' THEN prices.PricePerUnit ELSE 0.0 END) AS "1yr Partial Upfront ($)"
FROM prices
--WHERE "Instance Type" = "t2.small" AND Region = "eu-west-1"
GROUP BY
	"Region", 	
	"Operating System",
	"Pre Installed S/W",
	"Instance Type"
ORDER BY 
	"Region", 	
	"Operating System",
	"Pre Installed S/W",
	"Instance Type"
;

select * from ec2 where SKU = "N6KDUVR23T758UUC";
select * from ec2 where RateCode = "3PG53PNNDP3K6N2V.6QCMYABX3D.2TG2D8R56U" OR RateCode = "UY4SMYZQCJ58224G.6QCMYABX3D.2TG2D8R56U";
select * from ec2 where RateCode = "N6KDUVR23T758UUC.HU7G6KETJZ.2TG2D8R56U";