#Retrieve tasks where the person in charge of a project is a specific user.
SELECT 
    task_id, 
    task_name, 
    task_type, 
    status, 
    workflow_name, 
FROM 
    wedata.task
WHERE 
    project_id = 'your_project_id'  -- Replace with your project ID
    AND owner = 'your_owner_name';  -- Replace with your owner name

#Retrieve a list of tasks with a scheduling period of one day for a specific project.
SELECT 
    project_id, 
    project_name, 
    task_id, 
    task_name, 
    config_method, 
    schedule_cycle_type, 
    effective_start_time, 
    effective_end_time, 
    schedule_frequency
FROM 
    wedata.task_schedule
WHERE 
    project_id = 'your_project_id'  -- Replace with your project ID
    AND schedule_frequency = 'DAY_CYCLE';  


#Get the direct upstream task of a certain task
SELECT 
    dependency_project_id AS upstream_project_id, 
    dependency_project_name AS upstream_project_name, 
    dependency_task_id AS upstream_task_id, 
    dependency_task_name AS upstream_task_name
FROM 
    wedata.task_dependency
WHERE 
    task_id = 'your_task_id';  -- Replace with your task ID


#Retrieve the workflow list under a specific username
SELECT 
    workflow_id, 
    workflow_name, 
    workflow_type, 
FROM 
    wedata.workflow
WHERE 
    owner = 'your_owner_name';   -- Replace with your owner name

#Get the number of times each workflow's task instances have run in the past 7 days.
SELECT 
    workflow_name, 
    COUNT(task_run_id) AS instance_count
FROM 
    wedata.task_run_record
WHERE 
    run_start_dttm >= CURRENT_DATE - INTERVAL '7' DAY
GROUP BY 
    workflow_name;
#Retrieve the top 10 tasks by average runtime and return the task name and average runtime.
SELECT 
    task_name, 
    AVG(run_min) AS avg_run_min
FROM 
    wedata.task_run_record
WHERE 
    run_min IS NOT NULL
GROUP BY 
    task_name
ORDER BY 
    avg_run_min DESC
LIMIT 10;

#Get the actual parameters and corresponding values ​​used in a task.
WITH task_params AS (
    SELECT 
        p.param_name, 
        p.param_value_dev AS dev_value, 
        p.param_value_prod AS prod_value, 
        'TASK' AS param_level
    FROM 
        wedata.parameter p
    JOIN 
        wedata.task t ON p.id = t.task_id
    WHERE 
        t.task_id = 'task_id'  -- Replace with your task ID
        AND p.level = 'TASK'
),
workflow_params AS (
    SELECT 
        p.param_name, 
        p.param_value_dev AS dev_value, 
        p.param_value_prod AS prod_value, 
        'WORKFLOW' AS param_level
    FROM 
        wedata.parameter p
    JOIN 
        wedata.task t ON p.id = t.workflow_id
    WHERE 
        t.task_id = 'task_id'  -- Replace with your task ID
        AND p.level = 'WORKFLOW'
),
project_params AS (
    SELECT 
        p.param_name, 
        p.param_value_dev AS dev_value, 
        p.param_value_prod AS prod_value, 
        'PROJECT' AS param_level
    FROM 
        wedata.parameter p
    JOIN 
        wedata.task t ON p.id = t.project_id
    WHERE 
        t.task_id = 'task_id'  -- Replace with your task ID
        AND p.level = 'PROJECT'
),
all_params AS (
    SELECT * FROM task_params
    UNION ALL
    SELECT * FROM workflow_params
    UNION ALL
    SELECT * FROM project_params
)
SELECT 
    param_name, 
    dev_value, 
    prod_value, 
    param_level
FROM (
    SELECT 
        param_name, 
        dev_value, 
        prod_value, 
        param_level, 
        ROW_NUMBER() OVER (
            PARTITION BY param_name 
            ORDER BY CASE 
                WHEN param_level = 'TASK' THEN 1 
                WHEN param_level = 'WORKFLOW' THEN 2 
                WHEN param_level = 'PROJECT' THEN 3 
            END
        ) AS rn
    FROM 
        all_params
) ranked_params
WHERE 
    rn = 1;