SHOW SCHEMAS IN workspace;
SHOW SCHEMAS EXTENDED IN workspace;                          -- + num_tables
SHOW TABLES IN workspace.public LIKE '*flujos*';
SHOW TABLES IN workspace.public LIKE '*ngs';                 -- terminan en 'ngs'
SHOW TABLE EXTENDED IN workspace.public LIKE 'monitoreo_*';  -- metadatos por tabla

-- Buscar toda columna que contenga "fecha"
SELECT table_name, column_name, data_type
FROM workspace.information_schema.columns
WHERE column_name LIKE '%fecha%'
ORDER BY table_name;

-- Conteo de columnas por tabla
SELECT table_name, COUNT(*) AS num_columnas
FROM workspace.information_schema.columns
GROUP BY table_name ORDER BY num_columnas DESC;

-- Columnas comunes entre dos tablas (CTE + JOIN)
WITH a AS (SELECT column_name FROM workspace.information_schema.columns WHERE table_name = 'bronze_flujos'),
     b AS (SELECT column_name FROM workspace.information_schema.columns WHERE table_name = 'bronze_informes')
SELECT a.column_name FROM a INNER JOIN b ON a.column_name = b.column_name;

DESCRIBE TABLE workspace.public.flujos_migratorios_estorninos;
DESCRIBE TABLE EXTENDED workspace.public.flujos_migratorios_estorninos;
DESCRIBE HISTORY workspace.public.flujos_migratorios_estorninos LIMIT 10;

-- Vista general + conteo
SELECT * FROM bronze_ventas LIMIT 10;
SELECT COUNT(*) AS total FROM bronze_ventas;

-- Rango de fechas (DATEDIFF traducido a Postgres)
SELECT MIN(fecha) AS min, MAX(fecha) AS max,
       DATEDIFF(day, MIN(fecha), MAX(fecha)) AS dias_de_datos
FROM bronze_ventas;

-- Distribución por categoría
SELECT producto, COUNT(*) AS n, SUM(cantidad) AS total,
       ROUND(AVG(precio_unitario), 2) AS precio_medio
FROM bronze_ventas GROUP BY producto ORDER BY n DESC;

-- Estadísticas descriptivas
SELECT COUNT(*) AS tx, SUM(cantidad*precio_unitario) AS ingreso,
       ROUND(AVG(cantidad*precio_unitario),2) AS ticket_medio,
       ROUND(STDDEV(cantidad*precio_unitario),2) AS desv_std
FROM bronze_ventas;

-- Outliers (subquery sobre la misma tabla)
SELECT producto, cantidad, precio_unitario
FROM bronze_ventas
WHERE cantidad > (SELECT AVG(cantidad) + 2*STDDEV(cantidad) FROM bronze_ventas)
ORDER BY cantidad DESC;

-- Completitud: % de nulos por columna
SELECT COUNT(*) AS total,
  ROUND(100.0*SUM(CASE WHEN especie_nombre_comun IS NULL THEN 1 ELSE 0 END)/COUNT(*),2) AS pct_especie_null,
  ROUND(100.0*SUM(CASE WHEN velocidad_promedio_kmh IS NULL THEN 1 ELSE 0 END)/COUNT(*),2) AS pct_vel_null
FROM bronze_flujos;

-- Unicidad: IDs duplicados
SELECT id, COUNT(*) AS veces FROM bronze_flujos
GROUP BY id HAVING COUNT(*) > 1;

-- Rangos válidos
SELECT id, velocidad_promedio_kmh, altitud_vuelo_metros FROM bronze_flujos
WHERE velocidad_promedio_kmh < 0 OR velocidad_promedio_kmh > 200
   OR altitud_vuelo_metros < 0 OR altitud_vuelo_metros > 15000;

-- Consistencia cross-table (NOT EXISTS, multi-tabla)
SELECT DISTINCT f.especie_nombre_comun FROM bronze_flujos f
WHERE NOT EXISTS (SELECT 1 FROM bronze_informes i WHERE i.especie = f.especie_nombre_comun);

-- Data profiling
SELECT COUNT(DISTINCT especie_nombre_comun) AS unicos,
       MIN(LENGTH(especie_nombre_comun)) AS len_min,
       MAX(LENGTH(especie_nombre_comun)) AS len_max
FROM bronze_flujos;

-- CTE + window (porcentaje del total)
WITH stats AS (
  SELECT clima, COUNT(*) AS n
  FROM bronze_flujos GROUP BY clima
)
SELECT clima, n, ROUND(100.0*n/SUM(n) OVER(), 2) AS pct
FROM stats ORDER BY n DESC;

SELECT especie_nombre_comun, fecha_avistamiento,
       DATEDIFF(day, fecha_avistamiento, CURRENT_TIMESTAMP()) AS dias
FROM bronze_flujos
WHERE especie_cientifico RLIKE '^[SA]'
ORDER BY dias LIMIT 10;

-- Un dataset normal (verde) → capa bronze
ALTER TABLE ventas RENAME TO bronze_ventas;

-- Varias a la vez (batch, admite comentarios)
ALTER TABLE flujos RENAME TO bronze_flujos;
ALTER TABLE informes RENAME TO bronze_informes;

DESCUBRIMIENTO   SHOW SCHEMAS|TABLES [EXTENDED] [IN …] [LIKE '<glob>']
                 SHOW TABLE EXTENDED IN … LIKE '<glob>'
                 DESCRIBE [TABLE] [EXTENDED] <ref> · DESCRIBE HISTORY <ref> [LIMIT n]
                 SELECT … FROM <cat>.information_schema.columns
EDA / CALIDAD    SELECT · COUNT · MIN/MAX/AVG/SUM/STDDEV · GROUP BY · HAVING
                 CASE WHEN · subqueries · DISTINCT · LENGTH
AD-HOC           JOIN (INNER/LEFT/RIGHT/FULL/CROSS) · STRING_AGG · window OVER()
                 PERCENTILE_CONT WITHIN GROUP · WITH … (CTEs)
DIALECTO         CURRENT_TIMESTAMP()/DATE() · RLIKE · APPROX_COUNT_DISTINCT
                 REGEXP_EXTRACT(str,pat[,idx]) · REGEXP_REPLACE(str,pat,repl)
                 GET_JSON_OBJECT(col,'$.a.b')
                 DATE_ADD/SUB · ADD_MONTHS · MONTHS_BETWEEN · DATEDIFF(unit,a,b)
                 UNIX_TIMESTAMP · FROM_UNIXTIME · DAYOFWEEK · WEEKOFYEAR · LAST_DAY
TIERING          ALTER TABLE <t> RENAME TO bronze_|silver_|gold_<t>