-- CREATE TABLE test (
-- id serial,
-- name varchar
-- );
-- INSERT INTO test (name) values ('one'),('two'),('three'),('four'),('five');
-- SELECT *
-- FROM test
-- ORDER BY id DESC
-- OFFSET 1 LIMIT 1;
-- CREATE SCHEMA public;
CREATE TABLE IF NOT EXISTS public.hw(
id INTEGER,
height FLOAT4,
weight FLOAT4
);
SELECT min(height) as min_height, max(height) as max_height, min(weight) as min_weight, max(weight) as max_weight FROM public.hw;
SELECT (weight * 0.453592)/POWER(height * 0.0254, 2) AS bmi FROM public.hw;
INSERT INTO public.hw values (1, 1.0, 0.0);
INSERT INTO public.hw values (2, 1.0, 100.0);
INSERT INTO public.hw values (3, 1.0, 100.0);
SELECT COUNT(id) AS underweight_count FROM public.hw WHERE (weight / 2.2046) / POWER(height * 0.0254, 2) < 18.5;
SELECT id, (weight * 0.453592)/POWER(height * 0.0254, 2) AS bmi,
CASE
WHEN (weight * 0.453592)/POWER(height * 0.0254, 2) < 18.5 THEN 'underweight'
WHEN (weight * 0.453592)/POWER(height * 0.0254, 2) < 25 THEN 'normal'
WHEN (weight * 0.453592)/POWER(height * 0.0254, 2) < 30 THEN 'overweight'
WHEN (weight * 0.453592)/POWER(height * 0.0254, 2) < 35 THEN 'obese'
ELSE 'extremely obese'
END AS type
FROM public.hw ORDER BY bmi DESC, id DESC;
CREATE TABLE IF NOT EXISTS coins(
dt VARCHAR(16),
full_nm VARCHAR(128),
high_price NUMERIC,
tx_cnt NUMERIC,
vol NUMERIC
);
-- INSERT INTO coins values (1, 101, 1, 1, 1, 1, 'BiTCoin';
-- INSERT INTO coins values (1, 1, 1, 1, 1, 1, 'BiTCOin');
-- SELECT * FROM coins WHERE full_nm = 'BTC' and avg_price < 100;
-- SELECT dt, high_price, vol FROM public.coins WHERE symbol = 'DOGE' AND avg_price > 0.001 AND dt LIKE '2018%';
SELECT DISTINCT UPPER(full_nm) AS full_name FROM public.coins ORDER BY full_name;
INSERT INTO coins values ('2021-01-01', 'bitcoin', 120, 1, 1);
INSERT INTO coins values ('2021-01-02', 'bitcoin', 120);
WITH
best_price AS (
SELECT
full_nm,
MAX(high_price) as price
FROM
public.coins
GROUP BY
full_nm
)
SELECT UPPER(p.full_nm) AS full_name, MIN(dt) AS dt, p.price FROM public.coins AS c RIGHT JOIN best_price AS p ON c.full_nm = p.full_nm AND c.high_price = p.price GROUP BY p.full_nm, p.price ORDER BY price DESC, full_name ASC;
-- SELECT DISTINCT UPPER(full_nm), MIN(dt), high_price AS price FROM public.coins RIGHT JOIN best_price ON public.coins.full_nm = best_price.full_nm GROUP BY full_nm, high_price;
-- SELECT DISTINCT UPPER(full_nm) AS full_name, MIN(dt), high_price AS price FROM public.coins;
DELETE FROM public.coins WHERE tx_cnt IS NULL OR vol IS NULL;
SELECT * FROM public.coins;
Telegram: DB developers channel