सामग्री

• 
  
• SQL
  
• 
  
  • № 1.
    
  • № 2.
    
  • № 3. ( )
    
    • 1
      
    • 2
      
    • 3
  • № 4.
    
  • № 5.
    
  • № 6.
• 
  
  • № 1.
    
  • № 2. ( )
    
    • 1
      
    • 2
• 
  
  • № 1.
    
  • № 2. ( )
    
    • 1
      
    • 2
  • № 3.

मान लिया गया है और मैनुअल का उपयोग कैसे करें

• व्हाइटबोर्ड या नोटपैड के साथ एक साक्षात्कार के दौरान मामूली वाक्यविन्यास त्रुटियां ज्यादा मायने नहीं रखती हैं। लेकिन वे साक्षात्कारकर्ता को विचलित कर सकते हैं, इसलिए आदर्श रूप से तर्क पर अपना ध्यान केंद्रित करने के लिए उनकी संख्या को कम करने का प्रयास करें।
  
• आवश्यक रूप से दिए गए उत्तर प्रत्येक समस्या को हल करने का एकमात्र तरीका नहीं हैं। अतिरिक्त समाधान के साथ टिप्पणी लिखने के लिए स्वतंत्र महसूस करें जिसे आप इस गाइड में जोड़ सकते हैं!

एसक्यूएल साक्षात्कार में जटिल कार्यों को हल करने के लिए टिप्स

1. समस्या के वर्णन को ध्यान से सुनें, साक्षात्कारकर्ता को समस्या का सार दोहराएं
   
2. एक बॉर्डरलाइन मामले को प्रदर्शित करें कि आप वास्तव में समस्या को समझते हैं (यानी, एक पंक्ति जिसे अंतिम SQL क्वेरी में शामिल नहीं किया जाएगा जिसे आप लिखने जा रहे हैं)
   
3. ( ) , — : ,
   
   • , ,
4. SQL, , . , .

स्व-संघ कार्य

नंबर 1. प्रतिशत महीने में परिवर्तन

| user_id | तारीख |
| --------- | ------------ |
| 1 | 2018-07-01 |
| 234 | 2018-07-02 |
| 3 | 2018-07-02 |
| 1 | 2018-07-02 |
| ... | ... |
| 234 | 2018-10-04 |

WITH mau AS 
(
  SELECT 
   /* 
    *       
    *  , . .   ,    . 
    *    ,   
    *
    *  Postgres  DATE_TRUNC(),   
    *      SQL   
    * . https://www.postgresql.org/docs/9.0/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
    */ 
    DATE_TRUNC('month', date) month_timestamp,
    COUNT(DISTINCT user_id) mau
  FROM 
    logins 
  GROUP BY 
    DATE_TRUNC('month', date)
  )
 
 SELECT 
    /*
    *    SELECT      . 
    * 
    *        ,   
    *    ,   , 
    *        ..
    */ 
    a.month_timestamp previous_month, 
    a.mau previous_mau, 
    b.month_timestamp current_month, 
    b.mau current_mau, 
    ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change 
 FROM
    mau a 
 JOIN 
    /*
    *   `ON b.month_timestamp = a.month_timestamp + interval '1 month'` 
    */
    mau b ON a.month_timestamp = b.month_timestamp - interval '1 month' 

नंबर 2. पेड़ की संरचना को चिह्नित करना

नोड अभिभावक
12
२ ५
३ ५
४ ३
५ नल 

node    label  
1       Leaf
2       Inner
3       Inner
4       Leaf
5       Root

WITH join_table AS
(
    SELECT 
        cur.node, 
        cur.parent, 
        COUNT(next.node) AS num_children
    FROM 
        tree cur
    LEFT JOIN 
        tree next ON (next.parent = cur.node)
    GROUP BY 
        cur.node, 
        cur.parent
)

SELECT
    node,
    CASE
        WHEN parent IS NULL THEN "Root"
        WHEN num_children = 0 THEN "Leaf"
        ELSE "Inner"
    END AS label
FROM 
    join_table 

SELECT 
    node,
    CASE 
        WHEN parent IS NULL THEN 'Root'
        WHEN node NOT IN 
            (SELECT parent FROM tree WHERE parent IS NOT NULL) THEN 'Leaf'
        WHEN node IN (SELECT parent FROM tree) AND parent IS NOT NULL THEN 'Inner'
    END AS label 
 from 
    tree

नंबर 3. प्रति माह उपयोगकर्ता प्रतिधारण (कई भागों)

भाग 1

| user_id | तारीख |
| --------- | ------------ |
| 1 | 2018-07-01 |
| 234 | 2018-07-02 |
| 3 | 2018-07-02 |
| 1 | 2018-07-02 |
| ... | ... |
| 234 | 2018-10-04 |

SELECT 
    DATE_TRUNC('month', a.date) month_timestamp, 
    COUNT(DISTINCT a.user_id) retained_users 
 FROM 
    logins a 
 JOIN 
    logins b ON a.user_id = b.user_id 
        AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) + 
                                             interval '1 month'
 GROUP BY 
    date_trunc('month', a.date)

WITH DistinctMonthlyUsers AS (
  /*
  *     ** , 
  *  
  */
    SELECT DISTINCT
      DATE_TRUNC('MONTH', a.date) AS month_timestamp,
      user_id
    FROM logins
  )

SELECT
  CurrentMonth.month_timestamp month_timestamp,
  COUNT(PriorMonth.user_id) AS retained_user_count
FROM 
    DistinctMonthlyUsers AS CurrentMonth
LEFT JOIN 
    DistinctMonthlyUsers AS PriorMonth
  ON
    CurrentMonth.month_timestamp = PriorMonth.month_timestamp + INTERVAL '1 MONTH'
    AND 
    CurrentMonth.user_id = PriorMonth.user_id

भाग 2

SELECT 
    DATE_TRUNC('month', a.date) month_timestamp, 
    COUNT(DISTINCT b.user_id) churned_users 
FROM 
    logins a 
FULL OUTER JOIN 
    logins b ON a.user_id = b.user_id 
        AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) + 
                                         interval '1 month'
WHERE 
    a.user_id IS NULL 
GROUP BY 
    DATE_TRUNC('month', a.date)

भाग ३

| user_id | month_date |
| --------- | ------------ |
| 1 | 2018-05-01 |
| 234 | 2018-05-01 |
| 3 | 2018-05-01 |
| 12 | 2018-05-01 |
| ... | ... |
| 234 | 2018-10-01 |

WITH user_login_data AS 
(
    SELECT 
        DATE_TRUNC('month', a.date) month_timestamp,
        a.user_id,
        /* 
        *   ,    SQL,   , 
        *      SELECT   HAVING.
        *       .  
        */ 
        MAX(b.month_date) as most_recent_churn, 
        MAX(DATE_TRUNC('month', c.date)) as most_recent_active 
     FROM 
        logins a
     JOIN 
        user_churns b 
            ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) > b.month_date 
     JOIN
        logins c 
            ON a.user_id = c.user_id 
            AND 
            DATE_TRUNC('month', a.date) > DATE_TRUNC('month', c.date)
     WHERE 
        DATE_TRUNC('month', a.date) = DATE_TRUNC('month', current_timestamp)
     GROUP BY 
        DATE_TRUNC('month', a.date),
        a.user_id
     HAVING 
        most_recent_churn > most_recent_active

नंबर 4. कुल बढ़ाना

| तारीख | कैश_फ्लो |
| ------------ | ----------- |
| 2018-01-01 | -1000 |
| 2018-01-02 | -100 |
| 2018-01-03 | 50 |
| ... | ... |

| तारीख | संचयी_cf |
| ------------ | --------------- |
| 2018-01-01 | -1000 |
| 2018-01-02 | -1100 |
| 2018-01-03 | -1050 |
| ... | ... |

SELECT 
    a.date date, 
    SUM(b.cash_flow) as cumulative_cf 
FROM
    transactions a
JOIN b 
    transactions b ON a.date >= b.date 
GROUP BY 
    a.date 
ORDER BY 
    date ASC

SELECT 
    date, 
    SUM(cash_flow) OVER (ORDER BY date ASC) as cumulative_cf 
FROM
    transactions 
ORDER BY 
    date ASC

नंबर 5. मूविंग एवरेज

| तारीख | sign_ups |
| ------------ | ---------- |
| 2018-01-01 | 10 |
| 2018-01-02 | 20 |
| 2018-01-03 | 50 |
| ... | ... |
| 2018-10-01 | 35 |

SELECT 
  a.date, 
  AVG(b.sign_ups) average_sign_ups 
FROM 
  signups a 
JOIN 
  signups b ON a.date <= b.date + interval '6 days' AND a.date >= b.date
GROUP BY 
  a.date

नंबर 6. कई कनेक्शन की स्थिति

| आईडी | विषय | से | को | टाइमस्टैम्प |
| ---- | ---------- | ------------------ | -------------- | ------------------ |
| 1 | योसेमाइट | zach@g.com | thomas@g.com | 2018-01-02 12:45:03 |
| 2 | बड़ा सुर | sarah@g.com | thomas@g.com | 2018-01-02 16:30:01 |
| 3 | योसेमाइट | thomas@g.com | zach@g.com | 2018-01-02 16:35:04 |
| 4 | चल रहा है | jill@g.com | zach@g.com | 2018-01-03 08:12:45 |
| 5 | योसेमाइट | zach@g.com | thomas@g.com | 2018-01-03 14:02:01 |
| 6 | योसेमाइट | thomas@g.com | zach@g.com | 2018-01-03 15:01:05 |
| .. | .. | .. | .. | .. |

SELECT 
    a.id, 
    MIN(b.timestamp) - a.timestamp as time_to_respond 
FROM 
    emails a 
JOIN
    emails b 
        ON 
            b.subject = a.subject 
        AND 
            a.to = b.from
        AND 
            a.from = b.to 
        AND 
            a.timestamp < b.timestamp 
 WHERE 
    a.to = 'zach@g.com' 
 GROUP BY 
    a.id 

विंडो कार्यों के लिए कार्य

नंबर 1. अधिकतम मान वाले पहचानकर्ता का पता लगाएं

  depname | एम्पनो | वेतन |     
----------- + ------- + -------- +
 विकास | 11 | 5200 |
 विकास | 7 | 4200 |
 विकास | 9 | 4500 |
 विकास | 8 | 6000 |
 विकास | 10 | 5200 |
 कार्मिक | 5 | 3500 |
 कार्मिक | 2 | 3900 |
 बिक्री | 3 | 4800 |
 बिक्री | 1 | 5000 |
 बिक्री | 4 | 4800 |

WITH max_salary AS (
    SELECT 
        MAX(salary) max_salary
    FROM 
        salaries
    )
SELECT 
    s.empno
FROM 
    salaries s
JOIN 
    max_salary ms ON s.salary = ms.max_salary

WITH sal_rank AS 
  (SELECT 
    empno, 
    RANK() OVER(ORDER BY salary DESC) rnk
  FROM 
    salaries)
SELECT 
  empno
FROM
  sal_rank
WHERE 
  rnk = 1;

नंबर 2. विंडो फ़ंक्शन के साथ औसत मूल्य और रैंकिंग (कई भाग)

भाग 1

  depname | एम्पनो | वेतन |     
----------- + ------- + -------- +
 विकास | 11 | 5200 |
 विकास | 7 | 4200 |
 विकास | 9 | 4500 |
 विकास | 8 | 6000 |
 विकास | 10 | 5200 |
 कार्मिक | 5 | 3500 |
 कार्मिक | 2 | 3900 |
 बिक्री | 3 | 4800 |
 बिक्री | 1 | 5000 |
 बिक्री | 4 | 4800 |

  depname | एम्पनो | वेतन | avg_salary |     
----------- + ------- + -------- + ------------ +
 विकास | 11 | 5200 | 5020 |
 विकास | 7 | 4200 | 5020 |
 विकास | 9 | 4500 | 5020 |
 विकास | 8 | 6000 | 5020 |
 विकास | 10 | 5200 | 5020 |
 कार्मिक | 5 | 3500 | 3700 |
 कार्मिक | 2 | 3900 | 3700 |
 बिक्री | 3 | 4800 | 4867 |
 बिक्री | 1 | 5000 | 4867 |
 बिक्री | 4 | 4800 | 4867 |

SELECT 
    *, 
    /*
    * AVG() is a Postgres command, but other SQL flavors like BigQuery use 
    * AVERAGE()
    */ 
    ROUND(AVG(salary),0) OVER (PARTITION BY depname) avg_salary
FROM
    salaries

भाग 2

  depname | एम्पनो | वेतन | सैलरी_ट्रैक |     
----------- + ------- + -------- + ------------- +
 विकास | 11 | 5200 | 2 |
 विकास | 7 | 4200 | 5 |
 विकास | 9 | 4500 | 4 |
 विकास | 8 | 6000 | 1 |
 विकास | 10 | 5200 | 2 |
 कार्मिक | 5 | 3500 | 2 |
 कार्मिक | 2 | 3900 | 1 |
 बिक्री | 3 | 4800 | 2 |
 बिक्री | 1 | 5000 | 1 |
 बिक्री | 4 | 4800 | 2 |

SELECT 
    *, 
    RANK() OVER(PARTITION BY depname ORDER BY salary DESC) salary_rank
 FROM  
    salaries 

मध्यम और उच्च कठिनाई के अन्य कार्य

नंबर 1. हिस्टोग्राम

| session_id | length_seconds |
| ------------ | ---------------- |
| 1 | 23 |
| 2 | 453 |
| 3 | 27 |
| .. | .. |

| बाल्टी | गिनती |
| --------- | ------- |
| 20-25 | 2 |
| 450-455 | 1 |

WITH bin_label AS 
(SELECT 
    session_id, 
    FLOOR(length_seconds/5) as bin_label 
 FROM
    sessions 
 )
 SELECT 
    CONCATENTATE(STR(bin_label*5), '-', STR(bin_label*5+5)) bucket, 
    COUNT(DISTINCT session_id) count 
 GROUP BY 
    bin_label
 ORDER BY 
    bin_label ASC 

नंबर 2. क्रॉस कनेक्शन (कई भागों)

भाग 1

| राज्य | Total_streams |
| ------- | --------------- |
| एनसी | 34569 |
| एससी | 33999 |
| सीए | 98324 |
| एमए | 19345 |
| .. | .. |

| state_a | state_b |
| --------- | --------- |
| एनसी | एससी |
| एससी | एनसी |

SELECT
    a.state as state_a, 
    b.state as state_b 
 FROM   
    state_streams a
 CROSS JOIN 
    state_streams b 
 WHERE 
    ABS(a.total_streams - b.total_streams) < 1000
    AND 
    a.state <> b.state 

SELECT
    a.state as state_a, 
    b.state as state_b 
 FROM   
    state_streams a, state_streams b 
 WHERE 
    ABS(a.total_streams - b.total_streams) < 1000
    AND 
    a.state <> b.state 

भाग 2

SELECT
    a.state as state_a, 
    b.state as state_b 
 FROM   
    state_streams a, state_streams b 
 WHERE 
    ABS(a.total_streams - b.total_streams) < 1000
    AND 
    a.state > b.state 

नंबर 3. उन्नत गणना

| उपयोगकर्ता | वर्ग |
| ------ | ------- |
| 1 | ए |
| 1 | बी |
| 1 | बी |
| 2 | बी |
| 3 | ए |

| वर्ग | गिनती |
| ------- | ------- |
| ए | 1 |
| बी | 2 |

WITH usr_b_sum AS 
(
    SELECT 
        user, 
        SUM(CASE WHEN class = 'b' THEN 1 ELSE 0 END) num_b
    FROM 
        table
    GROUP BY 
        user
), 

usr_class_label AS 
(
    SELECT 
        user, 
        CASE WHEN num_b > 0 THEN 'b' ELSE 'a' END class 
    FROM 
        usr_b_sum
)

SELECT 
    class, 
    COUNT(DISTINCT user) count 
FROM
    usr_class_label
GROUP BY 
    class 
ORDER BY 
    class ASC

SELECT 
    "a" class,
    COUNT(DISTINCT user_id) - 
        (SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count 
UNION
SELECT 
    "b" class,
    (SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count