# -*- coding: utf-8 -*-
"""SQL_Queries.ipynb

Automatically generated by Colab.

Original file is located at
    https://colab.research.google.com/drive/1JMMq3yCv2xWTEZbs9S28qx4lXdF88M5d
"""

sql_queries = {
    'Count the number of records in the dataset': 'SELECT COUNT(*) FROM ved_test.synthetic_data;',
    'Get the average Weighted_Avg_PRB_Util_UL': 'SELECT AVG(Weighted_Avg_PRB_Util_UL) FROM ved_test.synthetic_data;',
    'Get the minimum UE_Pwr_Restricted_Pct value': 'SELECT MIN(UE_Pwr_Restricted_Pct) FROM ved_test.synthetic_data;',
    'Calculate the total UE_Pwr_Unrestricted_Pct_Num': 'SELECT SUM(UE_Pwr_Unrestricted_Pct_Num) FROM ved_test.synthetic_data;',
    'Get the distinct values of Network_Engineer': 'SELECT DISTINCT Network_Engineer FROM ved_test.synthetic_data;',
    'Count the number of records for each Network_Engineer': 'SELECT Network_Engineer, COUNT(*) FROM ved_test.synthetic_data GROUP BY Network_Engineer;',
    'Calculate the average RTT for each Network_Engineer': 'SELECT Network_Engineer, AVG(RTT) AS avg_rtt FROM ved_test.synthetic_data GROUP BY Network_Engineer;',
    'Calculate the average Jitter for weekends and weekdays': 'SELECT WEEKEND, AVG(Jitter) AS avg_jitter FROM ved_test.synthetic_data GROUP BY WEEKEND;',
    'Get the average Jitter and Packet Loss for each hour': 'SELECT Hour, AVG(Jitter) AS avg_jitter, AVG(`DL Packet Loss Pct`) AS avg_packet_loss FROM ved_test.synthetic_data GROUP BY Hour ORDER BY Hour;',
    'Find the top 5 records with the highest UPTP_Mbps': 'SELECT * FROM ved_test.synthetic_data ORDER BY UPTP_Mbps DESC LIMIT 5;',
    'Calculate the average HARQ_BLER_Pct for each 5G_Reliability_Category': 'SELECT `5G Reliability Category`, AVG(HARQ_BLER_Pct) AS avg_harq_bler_pct FROM ved_test.synthetic_data GROUP BY `5G Reliability Category`;',
    'Find the average RTT and Jitter for each combination of Market and Hour': 'SELECT Market, Hour, AVG(RTT) AS avg_rtt, AVG(Jitter) AS avg_jitter FROM ved_test.synthetic_data GROUP BY Market, Hour;',
    'Get the top 3 Network_Engineers with the highest average 5G_Reliability_Score': 'SELECT Network_Engineer, AVG(`5G Reliability Score`) AS avg_reliability_score FROM ved_test.synthetic_data GROUP BY Network_Engineer ORDER BY avg_reliability_score DESC LIMIT 3;',
    'Calculate the average Weighted_Avg_PRB_Util_UL and DL_MAC_Vol_Scell_Pct for each day of the week': 'SELECT EXTRACT(DAYOFWEEK FROM Timestamp) AS day_of_week, AVG(Weighted_Avg_PRB_Util_UL) AS avg_prb_util, AVG(`DL MAC Vol Scell Pct`) AS avg_dl_mac_vol FROM ved_test.synthetic_data GROUP BY day_of_week;',
    'Get the average HARQ_BLER_Pct for each Market on weekends': 'SELECT Market, AVG(HARQ_BLER_Pct) AS avg_harq_bler_pct FROM ved_test.synthetic_data WHERE WEEKEND = 1 GROUP BY Market;',
    'Calculate the total Bearer Releases for records where Bearer_Setup_Failure_Pct is greater than 80% and group by Market': 'SELECT Market, SUM(`Bearer Releases`) AS total_bearer_release FROM ved_test.synthetic_data WHERE Bearer_Setup_Failure_Pct > 0.8 GROUP BY Market;',
    'Find the Market with the highest total UE_Pwr_Unrestricted_Pct_Num and the total value': 'SELECT Market, SUM(UE_Pwr_Unrestricted_Pct_Num) AS total_unrestricted_pwr FROM ved_test.synthetic_data GROUP BY Market ORDER BY total_unrestricted_pwr DESC LIMIT 1;',
    'Get the average UPTP_Mbps and RRC_Reestab_Attempts for each Market for weekdays and weekends': 'SELECT Market, WEEKEND, AVG(UPTP_Mbps) AS avg_uptp_mbps, AVG(RRC_Reestab_Attempts) AS avg_rrc_attempts FROM ved_test.synthetic_data GROUP BY Market, WEEKEND;',
    'Find the correlation between RTT and Jitter for each Market': 'SELECT Market, CORR(RTT, Jitter) AS rtt_jitter_correlation FROM ved_test.synthetic_data GROUP BY Market;',
    'Calculate the average Weighted_Avg_PRB_Util_UL for each day of the week': 'SELECT EXTRACT(DAYOFWEEK FROM Timestamp) AS day_of_week, AVG(Weighted_Avg_PRB_Util_UL) AS avg_prb_util FROM ved_test.synthetic_data GROUP BY day_of_week;',
    'Calculate the average UPTP_Mbps for each 5G_Reliability_Category during weekdays and weekends': 'SELECT `5G Reliability Category`, WEEKEND, AVG(UPTP_Mbps) AS avg_uptp_mbps FROM ved_test.synthetic_data GROUP BY `5G Reliability Category`, WEEKEND ORDER BY `5G Reliability Category`, WEEKEND;',
    'Identify the hour with the highest average HARQ_BLER_Pct': 'SELECT Hour, AVG(HARQ_BLER_Pct) AS avg_harq_bler_pct FROM ved_test.synthetic_data GROUP BY Hour ORDER BY avg_harq_bler_pct DESC LIMIT 1;',
    'Calculate the standard deviation of 5G_Reliability_Score for each Network_Engineer': 'SELECT Network_Engineer, STDDEV(`5G Reliability Score`) AS stddev_reliability_score FROM ved_test.synthetic_data GROUP BY Network_Engineer;',
    'Find the top 3 hours with the highest total UE_Pwr_Unrestricted_Pct_Num during weekends': 'SELECT Hour, SUM(UE_Pwr_Unrestricted_Pct_Num) AS total_unrestricted_pwr FROM ved_test.synthetic_data WHERE WEEKEND = 1 GROUP BY Hour ORDER BY total_unrestricted_pwr DESC LIMIT 3;',
    'Determine the Network_Engineer with the highest average DL MAC Vol Scell Pct and the average value': 'SELECT Network_Engineer, AVG(`DL MAC Vol Scell Pct`) AS avg_dl_mac_vol FROM ved_test.synthetic_data GROUP BY Network_Engineer ORDER BY avg_dl_mac_vol DESC LIMIT 1;',
    'Find the variance in Jitter for each 5G_Reliability_Category': 'SELECT `5G Reliability Category`, VARIANCE(Jitter) AS variance_jitter FROM ved_test.synthetic_data GROUP BY `5G Reliability Category`;',
    'Calculate the correlation between RTT and 5G Reliability Value for each Reliability Category': 'SELECT `5G Reliability Category`, CORR(RTT, `5G Reliability Value`) AS rtt_reliability_correlation FROM ved_test.synthetic_data GROUP BY `5G Reliability Category`;',
    'Calculate the average and median MTTR for each Network Engineer filtered by 5G Reliability Value being above the overall average score': '''SELECT Network_Engineer, AVG(MTTR) AS avg_mttr, (
        SELECT AVG(middle_vals)
        FROM (
            SELECT MTTR AS middle_vals, ROW_NUMBER() OVER (PARTITION BY Network_Engineer ORDER BY MTTR) AS rnk, COUNT(*) OVER (PARTITION BY Network_Engineer) AS cnt
            FROM ved_test.synthetic_data
            WHERE `5G Reliability Value` > (SELECT AVG(`5G Reliability Value`) FROM ved_test.synthetic_data)
        ) AS subquery
        WHERE rnk IN (FLOOR((cnt + 1) / 2.0), FLOOR((cnt + 2) / 2.0))
    ) AS median_mttr
    FROM ved_test.synthetic_data
    WHERE `5G Reliability Value` > (SELECT AVG(`5G Reliability Value`) FROM ved_test.synthetic_data)
    GROUP BY Network_Engineer;''',
    'Find the count of records per 5G_Reliability_Category where 5G_Reliability_Value is below the average for the category': '''SELECT `5G Reliability Category`, COUNT(*) as count
    FROM ved_test.synthetic_data AS s1
    WHERE `5G Reliability Value` < (
      SELECT AVG(`5G Reliability Value`)
      FROM ved_test.synthetic_data AS s2
      WHERE s2.`5G Reliability Category` = s1.`5G Reliability Category`
    )
    GROUP BY `5G Reliability Category`;''',
    'Find the top 5 records with the highest HO Attempts for each Network_Engineer': '''SELECT s1.*
    FROM ved_test.synthetic_data s1
    JOIN (
      SELECT Network_Engineer, `HO Attempts`
      FROM (
        SELECT Network_Engineer, `HO Attempts`, ROW_NUMBER() OVER (PARTITION BY Network_Engineer ORDER BY `HO Attempts` DESC) AS rn
        FROM ved_test.synthetic_data
      ) temp
      WHERE rn <= 5
    ) s2
    ON s1.Network_Engineer = s2.Network_Engineer AND s1.`HO Attempts` = s2.`HO Attempts`;''',
    'Calculate the exponential moving average of 5G_Reliability_Value for each Network_Engineer with a smoothing factor of 0.1': '''WITH ema AS (
      SELECT Timestamp, Network_Engineer, `5G Reliability Value`, CAST(NULL AS FLOAT64) AS ema_value,
             ROW_NUMBER() OVER (PARTITION BY Network_Engineer ORDER BY Timestamp) AS row_num
      FROM ved_test.synthetic_data
    )
    SELECT a.Timestamp, a.Network_Engineer, a.`5G Reliability Value`,
           CASE
             WHEN a.row_num = 1 THEN a.`5G Reliability Value`
             ELSE (0.1 * a.`5G Reliability Value` + 0.9 * b.ema_value)
           END AS ema_value
    FROM ema a
    LEFT JOIN

 ema b
    ON a.Network_Engineer = b.Network_Engineer AND a.row_num = b.row_num + 1;''',
     "Find the records with the highest HARQ_BLER_Pct for each 5G_Reliability_Category": '''SELECT s1.*
FROM ved_test.synthetic_data s1
JOIN (
  SELECT `5G Reliability Category`, MAX(HARQ_BLER_Pct) AS max_harq_bler_pct
  FROM ved_test.synthetic_data
  GROUP BY `5G Reliability Category`
) s2
ON s1.`5G Reliability Category` = s2.`5G Reliability Category` AND s1.HARQ_BLER_Pct = s2.max_harq_bler_pct;''',

    "Identify the top 3 hours with the highest average UPTP_Mbps for each Market, including the variance in Jitter during these hours": '''WITH avg_uptp AS (
  SELECT Market, Hour, AVG(UPTP_Mbps) AS avg_uptp_mbps, VARIANCE(Jitter) AS jitter_variance,
         ROW_NUMBER() OVER (PARTITION BY Market ORDER BY AVG(UPTP_Mbps) DESC) AS rn
  FROM ved_test.synthetic_data
  GROUP BY Market, Hour
)
SELECT Market, Hour, avg_uptp_mbps, jitter_variance
FROM avg_uptp
WHERE rn <= 3;''',

    "Determine the Sector with the highest variance in 5G Reliability Value and its corresponding average Context Drop Percent": '''WITH variance_scores AS (
  SELECT Sector, VARIANCE(`5G Reliability Value`) AS score_variance, AVG(Context_Drop_Pct) AS avg_context_drop
  FROM ved_test.synthetic_data
  GROUP BY Sector
)
SELECT Sector, score_variance, avg_context_drop
FROM variance_scores
ORDER BY score_variance DESC
LIMIT 1;''',

    "Find hours where the average UPTP_Mbps is significantly different than the daily average (more than 2 standard deviations away from the mean)": '''WITH daily_stats AS (
    SELECT DATE(Timestamp) AS day, AVG(UPTP_Mbps) AS daily_avg_uptp, STDDEV(UPTP_Mbps) AS daily_stddev_uptp
    FROM ved_test.synthetic_data
    GROUP BY day
),
hourly_stats AS (
    SELECT DATE(Timestamp) AS day, EXTRACT(HOUR FROM Timestamp) AS hour, AVG(UPTP_Mbps) AS hourly_avg_uptp
    FROM ved_test.synthetic_data
    GROUP BY day, hour
)
SELECT
    hs.day, hs.hour, hs.hourly_avg_uptp, ds.daily_avg_uptp, ds.daily_stddev_uptp
FROM hourly_stats hs
JOIN daily_stats ds ON hs.day = ds.day
WHERE hs.hourly_avg_uptp > (ds.daily_avg_uptp + 2 * ds.daily_stddev_uptp)
   OR hs.hourly_avg_uptp < (ds.daily_avg_uptp - 2 * ds.daily_stddev_uptp)
ORDER BY hs.day, hs.hour;''',

    "Identify Days where more than 10% of the records have RRC Setup Failure above 25% for each region": '''WITH daily_rrc_failures AS (
    SELECT
        Region, DATE(Timestamp) AS day, COUNT(*) AS total_records,
        SUM(CASE WHEN `RRC Setup Failure% 5G`> 0.25 THEN 1 ELSE 0 END) AS high_failure_count
    FROM ved_test.synthetic_data
    GROUP BY Region, day
),
daily_rrc_failure_ratio AS (
    SELECT
        Region, day, total_records, high_failure_count, (high_failure_count / total_records) * 100 AS failure_ratio
    FROM daily_rrc_failures
)
SELECT
    Region, day, total_records, high_failure_count, failure_ratio
FROM daily_rrc_failure_ratio
WHERE failure_ratio > 10
ORDER BY Region, day;'''

}