Cheat Sheets

-- INNER keeps matches; LEFT keeps all left rows (NULLs where no match)
SELECT c.name, r.gpu_id
FROM customers c
LEFT JOIN rentals r ON r.customer_id = c.customer_id;

-- group + filter groups with HAVING (WHERE filters rows, HAVING filters groups)
SELECT gpu_id, SUM(hours) AS total_hours
FROM rentals
GROUP BY gpu_id
HAVING SUM(hours) > 100;

SELECT
  customer_id,
  started_at,
  amount,
  -- running total per customer over time
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY started_at) AS running_total,
  -- rank rentals within each customer (1 = most recent)
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY started_at DESC) AS rn,
  -- previous row's value
  LAG(amount) OVER (PARTITION BY customer_id ORDER BY started_at) AS prev_amount
FROM rentals;

-- TOP-N PER GROUP: rank, then filter in an outer query
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY gpu_id ORDER BY amount DESC) AS rn
  FROM rentals
) t
WHERE rn <= 3;

-- DEDUP: keep one row per key
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY rental_id ORDER BY loaded_at DESC) AS rn
  FROM bronze_rentals
) t WHERE rn = 1;

WITH monthly AS (
  SELECT date_trunc('month', started_at) AS month, SUM(amount) AS revenue
  FROM rentals GROUP BY 1
)
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly ORDER BY month;

pwd; ls -la; cd path        # where am I / what's here / move
cat f | grep "err" | wc -l  # pipe: read → filter → count
head -n 100 big.csv > sample.csv   # first 100 rows → new file
find . -name "*.parquet"    # find files by pattern
grep -r "TODO" .            # recursive text search

git status                  # what changed
git switch -c my-feature    # new branch
git add -A && git commit -m "msg"
git push -u origin my-feature   # then open a Pull Request
git switch main && git pull # sync main
git merge my-feature        # (or merge via the PR)

docker build -t myimg .     # build an image from a Dockerfile
docker run --rm myimg       # run a container
docker compose up -d        # start the whole stack (detached)
docker compose ps           # what's running
docker compose logs -f svc  # follow a service's logs
docker compose exec postgres psql -U postgres   # shell into a service
docker compose down         # stop it all

-- models/marts/fct_revenue.sql
SELECT gpu_id, SUM(hours * price_per_hour) AS revenue
FROM {{ ref('stg_rentals') }}
GROUP BY 1

models:
  - name: fct_revenue
    columns:
      - name: gpu_id
        tests: [not_null, unique]
      - name: revenue
        tests:
          - dbt_utils.accepted_range: {min_value: 0}

# dbt:  dbt build      (run models + tests)
#       dbt test       (just tests)
#       dbt docs generate && dbt docs serve

# Dagster — an asset with a dependency
from dagster import asset

@asset
def bronze_rentals() -> None:
    ...  # extract from Postgres

@asset(deps=[bronze_rentals])
def fct_revenue() -> None:
    ...  # transform
# run the UI:  dagster dev