It also seems like the station's paying price is almost always within 25% of the asking price.
produces:
So - the first one, well that seems feasible.
The one for Biowaste at Mirdi mcdivitt ... If someone can check that?
For now, I think I'll set a 25% threshold and if you type something over that I'll ask you to confirm.
Code:
C:\Dev\Trade> sqlite3 data\TradeDangerous.db
.mode col
.head on
-- Build a list of stations where the station charges
-- more for an item that it will pay to buy it back.
-- Also calculate the percentage of the asking price
-- (how much the station charges) of the diff.
DROP TABLE IF EXISTS t_differences;
CREATE TEMPORARY TABLE t_differences AS
SELECT sys.name, stn.name, itm.name,
sb.price as from_station,
ss.price as to_station,
ss.price - sb.price AS diff,
(ss.price - sb.price) * 100 / ss.price AS pct
FROM Item as itm
INNER JOIN StationSelling AS ss
USING (item_id)
INNER JOIN StationBuying AS sb
USING (station_id, item_id)
INNER JOIN Station AS stn
USING (station_id)
INNER JOIN System AS sys
USING (system_id)
WHERE ss.price > sb.price
;
-- List the p99.9
DROP TABLE IF EXISTS t_p999;
CREATE TEMPORARY TABLE t_p999 AS
SELECT 'p99.9 of percentages is:', pct FROM t_differences ORDER BY pct LIMIT 1
OFFSET (SELECT COUNT(*) FROM t_differences) * 999 / 1000 - 1;
SELECT * FROM t_p999;
-- List the items > the 99.9
SELECT * FROM t_differences WHERE pct > (SELECT pct from t_p999) ORDER BY pct ASC;
produces:
Code:
p99.9 of percentages is: 25
name name:1 name:2 from_station to_station diff pct
---------- ------------- ---------- ------------ ---------- ---------- ----------
Breksta Barcelos City Scrap 23 33 10 30
Hahgwe Skripochka Ho Tantalum 4063 6108 2045 33
70 Ophiuch Luk Station Semiconduc 583 906 323 35
Gulngaba Galindo Dock Indite 1843 2883 1040 36
Lu Yupik Bagian Survey Supercondu 3761 6834 3073 44
Kivah Sadi Carnot R Uraninite 479 906 427 47
Mirdi Mcdivitt Sett Biowaste 15 50 35 70
HIP 72353 Caidin Statio Beryllium 7677 76770 69093 90
So - the first one, well that seems feasible.
The one for Biowaste at Mirdi mcdivitt ... If someone can check that?
For now, I think I'll set a 25% threshold and if you type something over that I'll ask you to confirm.