Libreria di snippet

Snippet pronti e testati per Python, Flask, JavaScript, SQL, HTML, CSS e Bash. Aprili nel playground.

Mostra solo gli snippet salvati in questo browser

6 snippet

sql

Upsert: insert or update

Insert a row, or update it when the unique key already exists.

INSERT INTO page_views (path, day, views)
VALUES ('/pricing', CURRENT_DATE, 1)
ON CONFLICT (path, day)
DO UPDATE SET views = page_views.views + EXCLUDED.views;

intermediate #postgres#upsert#writes

sql

Find duplicate rows

List values that appear more than once, with their count.

SELECT lower(email) AS email, COUNT(*) AS copies
FROM users
GROUP BY lower(email)
HAVING COUNT(*) > 1
ORDER BY copies DESC;

beginner #data-quality#group-by

sql

Find slow queries

The queries using the most total time (needs the pg_stat_statements extension).

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT round(total_exec_time::numeric, 1) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,

advanced #postgres#performance#monitoring

sql

Keyset pagination

Page through big tables without the slowdown of large OFFSET values.

-- first page
SELECT id, title, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

intermediate #pagination#performance

sql

Running total

Cumulative revenue per day with SUM() OVER.

SELECT day,
       revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM (
    SELECT created_at::date AS day, SUM(total) AS revenue

intermediate #window-functions#reports

sql

Top N per group

The 3 best-selling products in every category with ROW_NUMBER().

SELECT category, product, revenue
FROM (
    SELECT c.name AS category,
           p.name AS product,
           SUM(oi.quantity * oi.unit_price) AS revenue,

advanced #window-functions#ranking#reports

PandaDev

Servizi

Catalogo app

Impara

Azienda

Aiuto

Cambia lingua