SQLize | PHPize | SQLtest

CREATE TABLE coins ( dt VARCHAR(16), avg_price NUMERIC, tx_cnt NUMERIC, tx_vol NUMERIC, active_addr_cnt NUMERIC, symbol VARCHAR(8), full_nm VARCHAR(128), open_price NUMERIC, high_price NUMERIC, low_price NUMERIC, close_price NUMERIC, vol NUMERIC, market NUMERIC ); INSERT INTO coins (dt, avg_price, tx_cnt, tx_vol, active_addr_cnt, symbol, full_nm, open_price, high_price, low_price, close_price, vol, market) VALUES ('2024-04-01', 100, 1000, 50000, 200, 'BTC', 'Bitcoin', 100, 110, 90, 100, 50000, 2000000), ('2024-04-02', 100, 1050, 52000, 220, 'BTC', 'Bitcoin', 100, 110, 90, 100, 52000, 2100000), ('2024-04-03', 100, 1100, 54000, 240, 'BTC', 'Bitcoin', 100, 110, 90, 100, 54000, 2200000), ('2024-04-04', 100, 1150, 56000, 260, 'BTC', 'Bitcoin', 100, 110, 90, 100, 56000, 2300000), ('2024-04-05', 100, 1200, 58000, 280, 'BTC', 'Bitcoin', 100, 110, 90, 100, 58000, 2400000), ('2024-04-06', 100, 1250, 60000, 300, 'BTC', 'Bitcoin', 100, 110, 90, 100, 60000, 2500000), ('2024-04-07', 100, 1300, 62000, 320, 'BTC', 'Bitcoin', 100, 110, 90, 100, 62000, 2600000), ('2024-04-08', 100, 1350, 64000, 340, 'BTC', 'Bitcoin', 100, 110, 90, 100, 64000, 2700000), ('2024-04-09', 100, 1400, 66000, 360, 'BTC', 'Bitcoin', 100, 110, 90, 100, 66000, 2800000), ('2024-04-10', 100, 1450, 68000, 380, 'BTC', 'Bitcoin', 100, 110, 90, 100, 68000, 2900000), ('2024-04-11', 50, 800, 40000, 180, 'ETH', 'Ethereum', 50, 60, 40, 50, 40000, 1500000), ('2024-04-12', 50, 850, 42000, 200, 'ETH', 'Ethereum', 50, 60, 40, 50, 42000, 1600000), ('2024-04-13', 50, 900, 44000, 220, 'ETH', 'Ethereum', 50, 60, 40, 50, 44000, 1700000), ('2024-04-14', 50, 950, 46000, 240, 'ETH', 'Ethereum', 50, 60, 40, 50, 46000, 1800000), ('2024-04-15', 50, 1000, 48000, 260, 'ETH', 'Ethereum', 50, 60, 40, 50, 48000, 1900000), ('2024-04-16', 50, 1050, 50000, 280, 'ETH', 'Ethereum', 50, 60, 40, 50, 50000, 2000000), ('2024-04-17', 50, 1100, 52000, 300, 'ETH', 'Ethereum', 50, 60, 40, 50, 52000, 2100000), ('2024-04-18', 50, 1150, 54000, 320, 'ETH', 'Ethereum', 50, 60, 40, 50, 54000, 2200000), ('2024-04-19', 50, 1200, 56000, 340, 'ETH', 'Ethereum', 50, 60, 40, 50, 56000, 2300000), ('2024-04-20', 50, 1250, 58000, 360, 'ETH', 'Ethereum', 50, 60, 40, 50, 58000, 2400000); CREATE OR REPLACE FUNCTION count_non_volatile_days(full_nm TEXT) RETURNS VOID AS $$ DECLARE count_days INTEGER, prev_price NUMERIC, current_price NUMERIC BEGIN SELECT COUNT(*) INTO count_days FROM ( SELECT *, LAG(close_price) OVER (ORDER BY dt) AS prev_close_price FROM coins WHERE full_nm = count_non_volatile_days.full_nm ) AS subquery WHERE prev_close_price IS NOT NULL AND close_price = prev_close_price; IF count_days IS NULL THEN: RAISE EXCEPTION 'Crypto currency with name "%"' || full_nm || '" is absent in database!' USING ERRCODE = '02000' END IF END;