SQL_EXAM

USE daraz_sql_exam;
SHOW TABLES;
SELECT * FROM daraz_sql_raw_data;

-- Question 01:
-- Write an SQL query to identify if a session is resolved or not?
-- Condition for resolved session:
-- 1. It can't be unsatisfied
-- 2. The last chat can't be a no answer, nor a recommendation & its not clicked.
-- 3. It can not be a click to agent, intention to agent, switch to agent or direct to agent session

-- Solution of Question 01:
SELECT * FROM daraz_sql_raw_data
WHERE
    is_session_unsatisfied = 'N'
AND is_session_last_chat_no_answer = 'N'
AND is_session_last_chat_recommend_not_clk = 'N'
AND is_session_click_to_agent = 'N'
AND is_session_intention_to_agent = 'N'
AND is_session_switch_to_agent = 'N'
AND is_session_direct_agent = 'N';


-- Question 02:
-- B. Write an SQL query that will help you get distinct count of total chats, total buyers, total sessions, total resolved sessions, total direct to agent sessions, total switch to agent sessions & segregate based on tenant.

SELECT
  tenant_name_new AS tenant,

  -- Unique counts
  COUNT(DISTINCT chat_uuid) AS total_chats,
  COUNT(DISTINCT buyer_xspace_id) AS total_buyers,
  COUNT(DISTINCT session_id) AS total_sessions,

  -- Resolved sessions: must follow resolution rules
  COUNT(DISTINCT IF(
    is_session_unsatisfied = 'N' AND
    is_session_last_chat_no_answer = 'N' AND
    is_session_last_chat_recommend_not_clk = 'N' AND
    is_session_click_to_agent = 'N' AND
    is_session_intention_to_agent = 'N' AND
    is_session_switch_to_agent = 'N' AND
    is_session_direct_agent = 'N',
    session_id,
    NULL
  )) AS total_resolved_sessions,

  -- Direct to agent sessions
  COUNT(DISTINCT IF(is_session_direct_agent = 'Y', session_id, NULL)) AS total_direct_to_agent_sessions,

  -- Switch to agent sessions
  COUNT(DISTINCT IF(is_session_switch_to_agent = 'Y', session_id, NULL)) AS total_switch_to_agent_sessions

FROM daraz_sql_raw_data
GROUP BY tenant_name_new
ORDER BY tenant_name_new;


-- Question 03
-- "C. Write an SQL query that helps you in calculating the channel wise resolution rate & transfer to agent rate provided that formulae are:
-- 1. resolution rate= total resolved sessions/(total sessions - total direct agent sessions)
-- 2. transfer to agent rate = (total switch to agent sessions + total direct to agent sessions)/total sessions"

-- Solution of question 03
SELECT
  channel_name_new AS channel,

  -- Resolution Rate (%)
  CONCAT(
    ROUND(
      COUNT(DISTINCT IF(
        is_session_unsatisfied = 'N' AND
        is_session_last_chat_no_answer = 'N' AND
        is_session_last_chat_recommend_not_clk = 'N' AND
        is_session_click_to_agent = 'N' AND
        is_session_intention_to_agent = 'N' AND
        is_session_switch_to_agent = 'N' AND
        is_session_direct_agent = 'N',
        session_id, NULL
      )) * 100.0 /
      NULLIF(COUNT(DISTINCT session_id) -
             COUNT(DISTINCT IF(is_session_direct_agent = 'Y', session_id, NULL)), 0),
      2
    ), '%'
  ) AS resolution_rate_percent,

  -- Transfer to Agent Rate (%)
  CONCAT(
    ROUND(
      (
        COUNT(DISTINCT IF(is_session_switch_to_agent = 'Y', session_id, NULL)) +
        COUNT(DISTINCT IF(is_session_direct_agent = 'Y', session_id, NULL))
      ) * 100.0 /
      NULLIF(COUNT(DISTINCT session_id), 0),
      2
    ), '%'
  ) AS transfer_to_agent_rate_percent

FROM daraz_sql_raw_data
GROUP BY channel_name_new
ORDER BY channel_name_new;