-- Recent queries
SELECT
  query_id,
  query_text,
  user_name,
  start_time,
  end_time,
  total_elapsed_time as duration_ms,
  bytes_scanned,
  rows_produced,
  warehouse_name
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD(hour, -24, CURRENT_TIMESTAMP()),
  END_TIME => CURRENT_TIMESTAMP()
))
ORDER BY start_time DESC;

-- Specific user queries
SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  USER_NAME => 'analyst@company.com',
  START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
));

-- Enable query profile
ALTER SESSION SET ENABLE_QUERY_PROFILE = TRUE;

-- Run query and check profile
SELECT * FROM large_table WHERE id = 123;

-- View profile
SELECT *
FROM TABLE(INFORMATION_SCHEMA.QUERY_PROFILE(
  QUERY_ID => LAST_QUERY_ID()
));

-- Find queries with most bytes scanned
SELECT
  query_text,
  total_elapsed_time,
  bytes_scanned / 1024 / 1024 / 1024 as gb_scanned,
  rows_produced,
  warehouse_name,
  compilation_time,
  execution_time,
  queue_time
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
))
WHERE bytes_scanned > 1024 * 1024 * 1024
ORDER BY bytes_scanned DESC
LIMIT 20;

-- Warehouse usage statistics
SELECT
  warehouse_name,
  COUNT(*) as query_count,
  AVG(total_elapsed_time) as avg_duration_ms,
  SUM(bytes_scanned) / 1024 / 1024 / 1024 as total_gb_scanned,
  SUM(credits_used) as total_credits
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
))
GROUP BY 1
ORDER BY total_gb_scanned DESC;

-- Check clustering depth
SELECT
  SYSTEM$CLUSTERING_DEPTH('database.schema.table');
  
-- Check clustering information
SELECT *
FROM TABLE(INFORMATION_SCHEMA.CLUSTERING_TABLE_INFO(
  'database.schema.table'
));

-- Recommend clustering key
SELECT *
FROM TABLE(INFORMATION_SCHEMA.CLUSTERING_RECOMMENDATIONS(
  'database.schema.table'
));

-- Cache performance
SELECT
  COUNT(*) as total_queries,
  SUM(CASE WHEN query_id IN (
    SELECT query_id FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
    WHERE execution_status = 'SUCCESS'
    AND total_elapsed_time < 100
  ) THEN 1 ELSE 0 END) as cached_queries,
  (cached_queries / total_queries * 100) as cache_hit_rate
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
));

-- Credit consumption by query type
SELECT
  CASE
    WHEN query_text LIKE '%SELECT%' THEN 'SELECT'
    WHEN query_text LIKE '%INSERT%' THEN 'INSERT'
    WHEN query_text LIKE '%UPDATE%' THEN 'UPDATE'
    WHEN query_text LIKE '%DELETE%' THEN 'DELETE'
    WHEN query_text LIKE '%COPY%' THEN 'COPY'
    ELSE 'Other'
  END as query_type,
  COUNT(*) as query_count,
  SUM(credits_used) as total_credits,
  AVG(credits_used) as avg_credits
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
))
GROUP BY 1
ORDER BY total_credits DESC;