Last Updated: October 13, 2021
·
389
· kalinin84

Statistics (PostgreSQL)

CREATE TABLE stars (
    id SERIAL PRIMARY KEY,
    rank INTEGER NOT NULL,
    score INTEGER NOT NULL,
    created_at TIMESTAMP NOT NULL
);
INSERT INTO stars VALUES 
(1, 1, 2, '2020-01-01 00:00:00'),
(2, 1, 2, '2020-01-01 00:01:00'),
(3, 1, 3, '2020-01-01 00:02:00'),
(4, 1, 4, '2020-01-01 00:03:00'),
(5, 2, 10, '2020-01-01 01:00:00'),
(6, 2, 20, '2020-01-01 01:01:00'),
(7, 2, 30, '2020-01-01 01:02:00'),
(8, 2, 20, '2020-01-01 01:03:00'),
(9, 2, 10, '2020-01-01 01:04:00');
SELECT 
    rank, 
    MIN(score),
    AVG(score),
    MAX(score)
FROM stars
GROUP BY rank
ORDER BY rank DESC;
SELECT 
    rank, 
    score,
    SUM(score) OVER (PARTITION BY rank) total_scores,
    DENSE_RANK() OVER (ORDER BY score) dense_rank
FROM stars;
SELECT 
    date_trunc('hour', created_at) AS hour, 
    array_agg(rank) AS ranks
FROM stars
GROUP BY hour;

2 Responses
Add your response

can you add description please,

9 months ago ·

please add description , Thanks by Fitonface

5 months ago ·