SELECT e.name, d.dept_name, COUNT(p.id) AS projects
FROM employees e
LEFT JOIN departments d  ON e.dept_id = d.id
LEFT JOIN projects p     ON e.id = p.owner_id
GROUP BY e.name, d.dept_name
HAVING COUNT(p.id) > 2;
-- LEFT JOIN keeps ALL rows from employees even with no match (NULLs)
-- INNER JOIN would silently drop employees with no dept or projects

-- Find top earner per department
WITH top_earners AS (
  SELECT *, RANK() OVER (
    PARTITION BY dept_id ORDER BY salary DESC
  ) AS rnk
  FROM employees
)
SELECT name, dept_id, salary FROM top_earners WHERE rnk = 1;

-- Rank each driver's worst speeding event per month
SELECT * FROM (
  SELECT driver_id,
         DATE_TRUNC('month', event_time) AS month,
         speed - speed_limit AS overspeed,
         RANK() OVER (
           PARTITION BY driver_id, DATE_TRUNC('month', event_time)
           ORDER BY speed - speed_limit DESC
         ) AS rnk
  FROM speeding_events
) t WHERE rnk = 1;

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

-- BAD output: reading 3421 rows to find 12
-- Seq Scan on orders  (cost=0.00..3421.00 rows=12)
--                     (actual time=0.021..45.2 rows=12)

-- GOOD after index:
-- Index Scan using idx_orders_customer on orders
--   (actual time=0.018..0.031 rows=12)

-- Slow: function prevents index use
WHERE DATE_TRUNC('month', created_at) = '2024-01-01'
-- Fast: range scan uses index
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'

SELECT vehicle_id,
       ROUND(AVG(speed), 2) AS avg_speed
FROM   gps_events
WHERE  recorded_at >= NOW() - INTERVAL '30 days'
  AND  speed > 0
GROUP  BY vehicle_id
ORDER  BY avg_speed DESC
LIMIT  10;

-- Partial: only index what you query (smaller, faster)
CREATE INDEX idx_active_orders ON orders(status) WHERE status != 'completed';

-- Covering: include extra cols → avoid heap lookup entirely
CREATE INDEX idx_orders_cover ON orders(customer_id) INCLUDE (total, created_at);

-- GIN for JSONB / spatial with PostGIS for geofencing
CREATE INDEX idx_geofences_geom ON geofences USING GiST(boundary);

-- Core entities
fleets      (id, customer_id, name)          -- tenancy boundary
vehicles    (id, fleet_id, plate, make, model, status)
drivers     (id, fleet_id, name, licence_no, status)

-- Assignment (many-to-many with time periods)
driver_vehicle_assignments
  (id, driver_id, vehicle_id, assigned_at, unassigned_at)

-- Trips (reconstructed from GPS events)
trips       (id, vehicle_id, driver_id, start_time, end_time,
             distance_km, start_lat, start_lng, end_lat, end_lng)

-- GPS events — PARTITION BY RANGE(recorded_at)
gps_events  (id, vehicle_id, trip_id, recorded_at,
             lat, lng, speed, heading, odometer)

-- Alerts (JSONB for flexible payload)
alerts      (id, vehicle_id, driver_id, alert_type,
             severity, triggered_at, resolved_at, payload jsonb)

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- both succeed together, or ROLLBACK to undo both

-- Monitor dead tuples
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

CREATE TABLE gps_events (
  id         BIGSERIAL,
  vehicle_id INT,
  recorded_at TIMESTAMPTZ NOT NULL,
  lat NUMERIC, lng NUMERIC, speed NUMERIC
) PARTITION BY RANGE(recorded_at);

CREATE TABLE gps_events_2024_q1 PARTITION OF gps_events
  FOR VALUES FROM('2024-01-01') TO('2024-04-01');

-- Drop old data cheaply — instant, no vacuum needed
DROP TABLE gps_events_2023_q1;

# Step 1: Identify the offending process
top          # sort by %CPU with 'P'
htop         # more visual; filter by process name
ps aux --sort=-%cpu | head -10

# Step 2: Inspect the process
pidstat -u 1 5    # per-process CPU usage over 5 seconds
systemctl status php-fpm
journalctl -u php-fpm --since "10 minutes ago"

# Step 3: Identify runaway threads or zombie processes
ps -eLf | grep php    # list all threads
ps aux | grep Z       # zombie processes

# Step 4: Check for CPU-heavy system calls
strace -p <PID> -c    # summarise system calls

# Also check: cron jobs, sustained vs spike, I/O waits

# Check current status and recent logs
systemctl status my-service
journalctl -u my-service -b         # logs since last boot
journalctl -u my-service -f         # follow live
journalctl -u my-service -n 100     # last 100 lines
systemctl cat my-service            # view unit file
systemctl list-dependencies my-service

# /etc/logrotate.d/telemetry
/var/log/telemetry/*.log {
    daily
    rotate 14
    compress
    delaycompress      # compress yesterday's log, not today's (safe for open handles)
    missingok
    notifempty
    sharedscripts
    postrotate
        systemctl reload telemetry-service
    endscript
}

#!/bin/bash
set -euo pipefail   # exit on error, unbound var, pipe fail

WATCH_DIR="/data/incoming"
LOG_FILE="/var/log/csv-processor.log"
DONE_DIR="/data/processed"

mkdir -p "$DONE_DIR"

log() { echo "$(date '+%Y-%m-%d %H:%M:%S') $*" >> "$LOG_FILE"; }

process_file() {
  local file="$1"
  local rows
  rows=$(tail -n +2 "$file" | wc -l)   # skip header
  log "Processed: $(basename "$file") | Rows: $rows"
  mv "$file" "$DONE_DIR/"
}

log "=== Watcher started ==="

inotifywait -m -e close_write "$WATCH_DIR" --format '%f' |
while read -r filename; do
  if [[ "$filename" == *.csv ]]; then
    process_file "$WATCH_DIR/$filename"
  fi
done

# Option 1: grep + sort + uniq (portable)
grep -oP 'vehicle_id=\K[^\s]+' fleet.log | sort -u

# Option 2: awk (faster for very large files)
awk 'match($0, /vehicle_id=([^ ]+)/, a) {print a[1]}' fleet.log | sort -u

# For 10GB+ files: use LC_ALL=C sort for much faster ASCII sorting
LC_ALL=C sort -u

git bisect start
git bisect bad                 # current commit has the bug
git bisect good v2.3.0         # this commit was known-good

# Git checks out the midpoint. Test it, then:
git bisect bad   # or
git bisect good

# Automate with a test script (exit 0 = good, non-zero = bad)
git bisect run ./test_script.sh

git bisect reset               # clean up when done

# Resource-oriented URLs
GET    /v1/fleets/{fleet_id}/vehicles
GET    /v1/vehicles/{vehicle_id}/trips?from=2024-01-01&to=2024-01-31
GET    /v1/vehicles/{vehicle_id}/position    # latest GPS fix
POST   /v1/alerts
PATCH  /v1/drivers/{driver_id}

// Blocking (bad — thread parked while DB responds)
public Vehicle GetVehicle(int id) {
    return _db.Vehicles.Find(id);
}

// Non-blocking (good — thread returns to pool during DB wait)
public async Task<Vehicle> GetVehicleAsync(int id) {
    return await _db.Vehicles.FindAsync(id);
}

// Bad — new array reference every render triggers re-render
const markers = vehicles.map(v => ({ lat: v.lat, lng: v.lng }));

// Good — memoised, only recalculates when vehicles changes
const markers = useMemo(
  () => vehicles.map(v => ({ lat: v.lat, lng: v.lng })),
  [vehicles]
);

# WRONG: cache busted every time source code changes
COPY . .
RUN npm ci     # reinstalls on every code change!

# CORRECT: deps layer cached unless package.json changes
COPY package*.json ./
RUN npm ci --production   # cached if deps unchanged
COPY . .                  # source invalidates cache only here

FROM php:8.3-fpm AS base
RUN docker-php-ext-install pdo pdo_pgsql

FROM base AS deps
COPY composer.json composer.lock ./
RUN composer install --no-dev --optimize-autoloader

FROM base AS final
COPY --from=deps /app/vendor ./vendor
COPY . .
EXPOSE 9000

---
# docker-compose.yml
services:
  api:
    build: .
    environment:
      DB_HOST: postgres
      DB_PASS: ${DB_PASS}   # from .env — never hardcode
    depends_on:
      postgres:
        condition: service_healthy
  postgres:
    image: postgres:16
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app"]
      interval: 5s

stages: [lint, test, build, deploy]

lint-code:
  stage: lint
  image: php:8.3
  script:
    - composer run phpcs       # PSR-12 coding standards
    - composer run phpstan     # static analysis
  cache:
    paths: [vendor/]
    key: $CI_COMMIT_REF_SLUG

unit-tests:
  stage: test
  services: [postgres:16]    # real DB for tests
  variables: {POSTGRES_DB: test_db}
  script:
    - composer run phpunit --coverage-text
  coverage: '/Lines:\s+(\d+\.\d+)%/'

build-image:
  stage: build
  image: docker:24
  services: [docker:dind]
  script:
    - docker build -t $CI_REGISTRY_IMAGE:$CI_COMMIT_SHA .
    - docker push $CI_REGISTRY_IMAGE:$CI_COMMIT_SHA

deploy-staging:
  stage: deploy
  script: [kubectl set image deployment/api api=$CI_REGISTRY_IMAGE:$CI_COMMIT_SHA]
  environment: staging
  rules:
    - if: $CI_COMMIT_BRANCH == "main"

deploy-production:
  stage: deploy
  when: manual         # human approval gate
  environment: production
  script: [kubectl set image deployment/api api=$CI_REGISTRY_IMAGE:$CI_COMMIT_SHA]
  rules:
    - if: $CI_COMMIT_TAG    # only on tagged releases