คำถามสัมภาษณ์ SQL สำหรับมืออาชีพที่มีประสบการณ์

บทนำ

 
หากคุณเป็นนักวิทยาศาสตร์ข้อมูลที่มีประสบการณ์และกำลังมองหางาน คุณไม่สามารถเลือกเวลาที่ดีกว่านี้ได้ ณ ตอนนี้ องค์กรที่จัดตั้งขึ้นจำนวนมากกำลังมองหานักวิทยาศาสตร์ด้านข้อมูลที่รู้จักฝีมือของตนทั้งภายในและภายนอก อย่างไรก็ตาม ความต้องการที่สูงไม่ได้หมายความว่าคุณสามารถหรือควรกระโดดข้ามห่วงและสมัครตำแหน่งระดับสูงโดยไม่มีชุดทักษะบางอย่าง เมื่อจ้างนักวิทยาศาสตร์ข้อมูลที่มีประสบการณ์ บริษัทต่างๆ คาดหวังให้พวกเขาทำงานที่ยากที่สุดที่อยู่ในมือ พนักงานเหล่านี้ควรมีความเข้าใจที่ดีแม้กระทั่งคุณลักษณะที่คลุมเครือที่สุด เพื่อให้สามารถใช้งานได้เมื่อจำเป็น

ไม่น่าแปลกใจเลยที่เมื่อสัมภาษณ์ตำแหน่งอาวุโส นักวิทยาศาสตร์ข้อมูลที่มีประสบการณ์มักจะถูกถามคำถามที่ยากกว่ามาก บ่อยครั้ง เมื่อทำงานเพียงงานเดียวเป็นเวลาสองปี นักวิทยาศาสตร์ด้านข้อมูลจะมีทักษะในการทำงานที่ซ้ำซากจำเจ จำเป็นสำหรับมืออาชีพที่จะต้องตระหนักว่า SQL ไม่ได้หยุดและจบลงด้วยความรู้ที่มีอยู่ เมื่อพูดถึงแนวคิด SQL ขั้นสูง อาจยังมีช่องว่างในความรู้อยู่บ้าง จึงไม่เจ็บที่จะได้รับความช่วยเหลือในการประสบความสำเร็จในการสัมภาษณ์นักวิทยาศาสตร์ข้อมูลของคุณ 

SQL เป็นภาษาหลักสำหรับการจัดการฐานข้อมูล ดังนั้นการดำเนินการ SQL จึงเป็นหัวใจสำคัญของงานของนักวิทยาศาสตร์ข้อมูล การสัมภาษณ์นักวิทยาศาสตร์ข้อมูลส่วนใหญ่จัดทำขึ้นเพื่อกำหนดความรู้ของผู้สมัครเกี่ยวกับ SQL 

งานประจำวันอาจไม่รวมถึงการเขียนคำถามที่ซับซ้อน แต่คุณต้องแสดงให้เห็นว่าหากจำเป็นต้องใช้ทักษะดังกล่าว คุณคือบุคคลที่สามารถทำได้ จึงไม่น่าแปลกใจเลยที่ผู้สัมภาษณ์จะถามกลุ่มต่างๆ ของ คำถามสัมภาษณ์ SQL เพื่อทดสอบความคล่องแคล่วของผู้สมัครใน SQL

ในบทความนี้ เราต้องการสรุปคำถามและแนวคิดที่ซับซ้อนบางส่วนที่ถามในการสัมภาษณ์กับผู้เชี่ยวชาญที่มีประสบการณ์ แม้ว่าคุณจะมั่นใจในความรู้ SQL ของคุณ แต่ก็ไม่เสียหายที่จะสแกนคำหลักและให้แน่ใจว่าคุณมีทุกสิ่งที่ครอบคลุม

แนวคิดขั้นต่ำเปลือยสำหรับมืออาชีพที่มีประสบการณ์

กรณี / เมื่อไร

 
การเข้าใจแนวคิดของ CASE อย่างถี่ถ้วน (และคำสั่ง When ที่มาพร้อมกัน) เป็นสิ่งจำเป็นสำหรับการบรรลุความเชี่ยวชาญของ SQL อย่างสมบูรณ์ คำชี้แจงกรณีศึกษาช่วยให้เราตรวจสอบเงื่อนไขบางอย่างและส่งกลับค่าโดยพิจารณาจากเงื่อนไขเหล่านั้นประเมินว่าจริงหรือเท็จ เมื่อใช้ร่วมกับอนุประโยค เช่น WHERE และ ORDER BY CASE ช่วยให้เราสามารถนำตรรกะ เงื่อนไข และลำดับมาสู่การสืบค้น SQL ของเรา

คุณค่าของคำสั่ง CASE ไม่ได้จำกัดอยู่เพียงแค่การให้ตรรกะแบบมีเงื่อนไขอย่างง่ายในการสืบค้นของเรา นักวิทยาศาสตร์ข้อมูลที่มีประสบการณ์ควรมีความเข้าใจในคำสั่งของ CASE และการใช้งานมากกว่าแค่ระดับพื้นผิว ผู้สัมภาษณ์มักจะถามคำถามคุณเกี่ยวกับสำนวนประเภทต่างๆ ของ CASE และวิธีเขียน 

ผู้สมัครที่มีประสบการณ์ควรเตรียมพร้อมที่จะตอบคำถามเชิงทฤษฎี เช่น เพื่ออธิบายความแตกต่างระหว่างข้อความ Valued และ Searched CASE วิธีทำงาน และวิธีเขียน สิ่งนี้ต้องการความเข้าใจอย่างถ่องแท้เกี่ยวกับไวยากรณ์และแนวทางปฏิบัติทั่วไป จำเป็นต้องพูด ซึ่งรวมถึงการใช้อนุประโยค ELSE อย่างเหมาะสมด้วย

นักวิทยาศาสตร์ข้อมูลที่มีประสบการณ์จะต้องรู้วิธีใช้ CASE กับฟังก์ชันรวมด้วยเช่นกัน คุณอาจถูกขอให้เขียนคำสั่ง CASE แบบย่อ ซึ่งไม่ซ้ำซากจำเจและเข้าใจง่าย คุณควรจะสามารถพูดคุยเกี่ยวกับคำเตือนและความเสี่ยงที่อาจเกิดขึ้นจากการใช้คำสั่งย่อของ CASE ได้อย่างชาญฉลาด

โดยทั่วไปแล้ว นักวิทยาศาสตร์ข้อมูลที่มีประสบการณ์จะต้องสามารถใช้ CASE เพื่อเขียนข้อความค้นหาที่มีประสิทธิภาพมากขึ้นได้ ท้ายที่สุด จุดประสงค์ทั้งหมดของคำสั่ง CASE คือการหลีกเลี่ยงการเขียนข้อความค้นหาแต่ละรายการมากเกินไปเพื่อรวบรวมข้อมูล

นี่คือตัวอย่างคำถามที่สามารถแก้ไขได้โดยใช้คำสั่ง CASE / WHEN: https://platform.stratascratch.com/coding/9634-host-response-rates-with-cleaning-fees?python= 

นี่เป็นคำถามที่ตอบยากในการสัมภาษณ์กับ Airbnb ซึ่งผู้สมัครจะต้องค้นหาอัตราการตอบกลับของเจ้าของที่พักโดยเฉลี่ย รหัสไปรษณีย์ และค่าธรรมเนียมการทำความสะอาดที่เกี่ยวข้อง

ในกรณีนี้ คำสั่ง CASE/ WHEN ใช้เพื่อจัดรูปแบบผลลัพธ์เป็นตัวเลขและแสดงเป็นค่าเปอร์เซ็นต์ นอกเหนือจากรหัสไปรษณีย์

การรวม SQL

 
เป็นเรื่องง่ายที่จะรู้สึกมั่นใจในความรู้ของคุณเกี่ยวกับการเข้าร่วม SQL แต่ยิ่งคุณสำรวจหัวข้อนี้มากเท่าไหร่ คุณจะยิ่งค้นพบว่าคุณไม่รู้มากขึ้นเท่านั้น ผู้สัมภาษณ์มักถาม คำถามสัมภาษณ์เกี่ยวกับแง่มุมขั้นสูงของ SQL Joins ที่มักถูกมองข้าม ดังนั้นสิ่งสำคัญคือต้องเจาะลึกแนวคิดนี้และฝึกฝนให้เชี่ยวชาญ
 
นอกจากแนวคิดพื้นฐานแล้ว ผู้สัมภาษณ์อาจสอบถามว่า self cross join คืออะไร และค้นหาความรู้เชิงลึกของคุณโดยถามเพื่อแก้ปัญหาเชิงปฏิบัติ คุณควรทราบชนิดของการรวมต่างๆ ทั้งหมด รวมถึงประเภทที่ซับซ้อนมากขึ้น เช่น การรวมแฮช หรือการรวมแบบผสม คุณอาจถูกขอให้อธิบายว่าการต่อแบบธรรมชาติคืออะไร และเมื่อใดจึงจะมีประโยชน์มากที่สุด บางครั้งคุณจะต้องอธิบายความแตกต่างระหว่างการต่อแบบธรรมชาติและแบบด้านใน
 
โดยทั่วไป คุณควรมีประสบการณ์และความชำนาญในการใช้การรวมร่วมกับข้อความอื่น ๆ เพื่อให้ได้ผลลัพธ์ที่ต้องการ ตัวอย่างเช่น คุณควรรู้วิธีใช้คำสั่ง WHERE เพื่อใช้งาน Cross Join ราวกับว่าเป็น Inner Join คุณจะต้องรู้วิธีใช้การรวมเพื่อสร้างตารางใหม่โดยไม่ต้องกดดันเซิร์ฟเวอร์มากเกินไป หรือวิธีใช้การรวมภายนอกเพื่อระบุและเติมค่าที่ขาดหายไปเมื่อทำการสอบถามฐานข้อมูล หรืองานภายในของการรวมภายนอก เช่น การจัดเรียงใหม่สามารถเปลี่ยนผลลัพธ์ได้ 

นี่คือตัวอย่างคำถามที่เกี่ยวข้องกับการเขียนข้อความร่วมภายใน. 

นี่เป็นคำถามที่ค่อนข้างยาก โดยที่ผู้สมัครจะถูกขอให้แสดงขนาดคำสั่งซื้อเป็นเปอร์เซ็นต์ของการใช้จ่ายทั้งหมด
 
 

แนวคิดขั้นสูง N1: การจัดการวันที่-เวลา

เป็นเรื่องปกติที่ฐานข้อมูลจะรวมวันที่และเวลา ดังนั้นนักวิทยาศาสตร์ข้อมูลที่มีประสบการณ์ควรมีความรู้เชิงลึกในการทำงานกับพวกเขา ข้อมูลประเภทนี้ช่วยให้เราสามารถติดตามลำดับเหตุการณ์ที่เกิดขึ้น การเปลี่ยนแปลงความถี่ ช่วงเวลาการคำนวณ และรับข้อมูลเชิงลึกที่สำคัญอื่นๆ หลายครั้งในการดำเนินการเหล่านี้จำเป็นต้องมีการจัดการวันที่-เวลาใน SQL อย่างสมบูรณ์ ดังนั้นมืออาชีพที่มีทักษะดังกล่าวจะมีข้อได้เปรียบเหนือผู้สมัครที่แข่งขันกัน หากคุณไม่มั่นใจในทักษะของคุณ 100% ให้ดูแนวคิดที่อธิบายไว้ด้านล่างและดูว่ามีกี่แนวคิดที่ฟังดูคุ้นเคย

เนื่องจากมีวิธีการจัดรูปแบบข้อมูลใน SQL ที่แตกต่างกันมากมาย (แต่ใช้ได้) ผู้เขียนโค้ดที่ยอดเยี่ยมจึงควรคุ้นเคยกับวิธีการเหล่านี้ทั้งหมดเป็นอย่างน้อย ในระหว่างการสัมภาษณ์ ผู้จัดการการจ้างงานคาดหวังความรู้เกี่ยวกับแนวคิดการจัดรูปแบบข้อมูลพื้นฐานและความสามารถในการพูดคุยอย่างชาญฉลาดเกี่ยวกับการเลือกฟังก์ชันที่เหมาะสมสำหรับงาน ซึ่งรวมถึงความรู้เกี่ยวกับฟังก์ชัน FORMAT() ที่สำคัญและไวยากรณ์ที่เกี่ยวข้องเพื่อใช้งานฟังก์ชันอย่างเต็มที่ นอกจากนี้ยังคาดหวังความรู้เกี่ยวกับฟังก์ชันพื้นฐานอื่นๆ เช่น NOW() นอกจากนี้ จะไม่เป็นปัญหาสำหรับมืออาชีพที่มีประสบการณ์ที่จะถามเกี่ยวกับแนวคิดพื้นฐาน เช่น ข้อมูลอนุกรมเวลาและจุดประสงค์

การพิจารณาบริบทของงานที่คุณสมัครเป็นสิ่งสำคัญเช่นกัน บริษัท AI หรือ IoT จะให้ความสำคัญกับการติดตามข้อมูลที่รวบรวมจากเซ็นเซอร์มากกว่า ในขณะที่แอปซื้อขายหุ้นอาจต้องการให้คุณติดตามความผันผวนของราคาในระหว่างวัน สัปดาห์ หรือเดือน

ในบางกรณี นายจ้างอาจถามเกี่ยวกับฟังก์ชันวันที่/เวลาขั้นสูงใน SQL เช่น CAST(), EXTRACT() หรือ DATE_TRUNC() ฟังก์ชันเหล่านี้มีค่ามากเมื่อคุณทำงานกับข้อมูลจำนวนมากที่มีวันที่ นักวิทยาศาสตร์ข้อมูลที่มีประสบการณ์ควรทราบวัตถุประสงค์ของแต่ละฟังก์ชันและการใช้งาน ในสถานการณ์ในอุดมคติ เขาหรือเธอควรมีประสบการณ์ในการใช้สิ่งเหล่านี้มาก่อน

การจัดการวันที่และเวลาที่ซับซ้อนที่สุดใน SQL จะเกี่ยวข้องกับการรวมกันของฟังก์ชันพื้นฐานและขั้นสูง ดังนั้นจึงจำเป็นต้องรู้ทั้งหมด โดยเริ่มจาก FORMAT(), NOW(),CURRENT_DATE และ CURRENT_TIME พื้นฐาน และรวมถึงฟังก์ชันขั้นสูงอื่นๆ ที่กล่าวถึงข้างต้น ในฐานะนักวิทยาศาสตร์ข้อมูลที่มีประสบการณ์ คุณควรรู้ว่า INTERVAL ทำอะไรและควรใช้เมื่อใด

นี่คือตัวอย่างคำถามที่ถามในการสัมภาษณ์กับ Airbnbโดยที่ผู้สมัครจะต้องใช้ข้อมูลที่มีอยู่เพื่อติดตามการเติบโตของ Airbnb
 
 

หลักฐาน:

 
ในคำถามนี้ ขอให้ผู้สมัครติดตามการเติบโตของ Airbnb โดยพิจารณาจากการเปลี่ยนแปลงจำนวนเจ้าของที่พักที่ลงทะเบียนในแต่ละปี กล่าวคือ เราจะใช้จำนวนโฮสต์ที่จดทะเบียนใหม่เป็นตัวบ่งชี้การเติบโตในแต่ละปี เราจะพบอัตราการเติบโตโดยการคำนวณส่วนต่างในจำนวนโฮสต์ระหว่างปีที่แล้วกับปีปัจจุบัน แล้วหารจำนวนนั้นด้วยจำนวนโฮสต์ที่ลงทะเบียนระหว่างปีที่แล้ว จากนั้นเราจะหาค่าเปอร์เซ็นต์โดยการคูณผลลัพธ์ด้วย 100

ตารางผลลัพธ์ควรมีคอลัมน์และข้อมูลที่สอดคล้องกันสำหรับจำนวนโฮสต์ในปีปัจจุบัน ในปีที่แล้ว และเปอร์เซ็นต์ของการเติบโตในแต่ละปี เปอร์เซ็นต์ต้องปัดเศษเป็นจำนวนเต็มที่ใกล้ที่สุด และต้องเรียงลำดับแถวจากน้อยไปมากขึ้นอยู่กับปี
 
 

วิธีการแก้:

 
ในการตอบคำถามนี้ ผู้สมัครต้องทำงานกับตารางที่เรียกว่า 'airbnb_search_details' ซึ่งประกอบด้วยคอลัมน์จำนวนมาก คอลัมน์ที่เราต้องการจะมีป้ายกำกับว่า 'host_since' ซึ่งระบุปี เดือน และวันที่โฮสต์ลงทะเบียนเว็บไซต์เป็นครั้งแรก สำหรับแบบฝึกหัดนี้ เดือนและวันจะไม่เกี่ยวข้องกัน ดังนั้นสิ่งแรกที่เราต้องทำคือดึงปีออกจากค่า จากนั้น เราจะต้องสร้างมุมมองที่มีคอลัมน์แยกสำหรับปีปัจจุบัน ปีที่แล้ว และจำนวนโฮสต์ทั้งหมดในปีนั้น 

เลือกการแยก (ปีจาก host_since::DATE) จาก airbnb_search_details โดยที่ host_since ไม่เป็นโมฆะ

จนถึงตอนนี้ เราได้ทำสองสิ่ง:

1. เราได้ตรวจสอบให้แน่ใจว่าได้รวมเฉพาะแถวที่คอลัมน์ host_since ไม่ว่างเปล่า
2. เราได้แยกปีออกจากข้อมูลและแปลงเป็นค่า DATE

เลือกการแยก (ปี FROM host_since::DATE) จำนวนนับ (id) เป็น current_year_host จาก airbnb_search_details โดยที่ host_since ไม่เป็น NULL GROUP BY แยก (ปีจาก host_since::DATE) เรียงลำดับตามปี asc

จากนั้นเราดำเนินการนับรหัสและตั้งค่าประโยค GROUP BY ทุกปี และทำให้แสดงผลตามลำดับจากน้อยไปมาก 

นี่ควรให้ตารางที่มีสองคอลัมน์แก่เรา: ปีและจำนวนโฮสต์ที่ลงทะเบียนในปีนั้น เรายังไม่มีภาพรวมที่จำเป็นในการแก้ปัญหา แต่เป็นขั้นตอนในทิศทางที่ถูกต้อง นอกจากนี้เรายังต้องการคอลัมน์แยกต่างหากสำหรับโฮสต์ที่ลงทะเบียนในปีที่แล้ว นี่คือที่มาของฟังก์ชัน LAG()

SELECT Year, current_year_host, LAG(current_year_host, 1) OVER (ORDER BY year) เป็น prev_year_host เลือกการแยก (ปีจาก host_since::DATE) นับ (id) เป็น current_year_host จาก airbnb_search_details โดยที่ host_since ไม่เป็น NULL GROUP แยกตามการแยก (ปีจาก host_since: :DATE) เรียงตามปี asc

ที่นี่ เราได้เพิ่มคอลัมน์ที่สาม ซึ่งจะมีชื่อว่า 'prev_year_host' และค่าของคอลัมน์นี้จะมาจาก 'current_year_host' ยกเว้นการหน่วงเวลาของหนึ่งแถว นี่คือสิ่งที่อาจดูเหมือน:

การจัดตารางด้วยวิธีนี้ทำให้สะดวกมากในการคำนวณอัตราการเติบโตขั้นสุดท้าย เรามีคอลัมน์แยกต่างหากสำหรับทุกค่าในสมการ ในที่สุดโค้ดของเราควรมีลักษณะดังนี้:

เลือกปี, current_year_host, prev_year_host, รอบ (((current_year_host - prev_year_host)/(cast(prev_year_host AS ตัวเลข)))*100) ประมาณการ_growth FROM (เลือก year, current_year_host, LAG(current_year_host, 1) OVER (เรียงตามลำดับปี) AS prev_year_host FROM (สารสกัด SELECT (ปี FROM host_since::date) AS year, count(id) current_year_host FROM airbnb_search_details WHERE host_since IS NOT NULL GROUP BY แยก (ปี FROM host_since::date) เรียงลำดับตามปี) t1) t2

ที่นี่ เราเพิ่มข้อความค้นหาอื่นและอีกคอลัมน์หนึ่งที่เราคำนวณอัตราการเติบโต เราต้องคูณผลลัพธ์เริ่มต้นด้วย 100 และปัดเศษเพื่อให้เป็นไปตามข้อกำหนดของงาน 

นั่นคือทางออกของงานนี้ เห็นได้ชัดว่าฟังก์ชันการจัดการวันที่-เวลามีความสำคัญต่อการทำงานให้เสร็จสิ้น 
 
 

แนวคิดขั้นสูง N2: ฟังก์ชันหน้าต่างและพาร์ติชัน

ฟังก์ชันหน้าต่าง SQL เป็นหนึ่งในแนวคิดที่สำคัญที่สุดสำหรับการเขียนแบบสอบถาม SQL ที่ซับซ้อนแต่มีประสิทธิภาพ ผู้เชี่ยวชาญที่มีประสบการณ์ได้รับการคาดหวังให้มีความรู้เชิงลึกเชิงปฏิบัติและเชิงทฤษฎีเกี่ยวกับฟังก์ชันหน้าต่าง ซึ่งรวมถึงรู้ว่าส่วนคำสั่งเกินคืออะไรและเข้าใจการใช้งานของมัน ผู้สัมภาษณ์อาจถามว่าคำสั่ง OVER สามารถเปลี่ยนฟังก์ชันการรวมเป็นฟังก์ชันหน้าต่างได้อย่างไร คุณอาจถูกถามเกี่ยวกับฟังก์ชันรวมสามฟังก์ชันที่สามารถใช้เป็นฟังก์ชันของหน้าต่างได้ นักวิทยาศาสตร์ข้อมูลที่มีประสบการณ์ควรทราบถึงฟังก์ชันหน้าต่างอื่นๆ ที่ไม่ใช่การรวมด้วยเช่นกัน

เพื่อให้ใช้ฟังก์ชันหน้าต่างได้ดีที่สุด เราต้องรู้ว่าคำสั่ง PARTITION BY คืออะไรและจะใช้งานอย่างไร คุณอาจถูกขอให้อธิบายและยกตัวอย่างกรณีการใช้งานบางกรณี บางครั้ง คุณจะต้องจัดระเบียบแถวภายในพาร์ติชั่นโดยใช้คำสั่งย่อย ORDER_BY

ผู้สมัครที่สามารถแสดงความรู้อย่างละเอียดเกี่ยวกับฟังก์ชันหน้าต่างแต่ละอัน เช่น ROW_NUMBER() จะได้รับการพิจารณาเป็นพิเศษ ไม่จำเป็นต้องพูด ความรู้เชิงทฤษฎีเพียงอย่างเดียวไม่เพียงพอ – ผู้เชี่ยวชาญควรมีประสบการณ์ในการใช้งานในทางปฏิบัติด้วย ไม่ว่าจะมีหรือไม่มีพาร์ติชันก็ตาม ตัวอย่างเช่น ผู้เชี่ยวชาญที่มีประสบการณ์ควรสามารถอธิบายความแตกต่างระหว่าง RANK() และ DENSE_RANK() ผู้ที่เหมาะสมควรทราบแนวคิดขั้นสูงบางอย่าง เช่น เฟรมภายในพาร์ติชั่น และสามารถอธิบายได้อย่างชัดเจน

ผู้สมัครที่ดีควรอธิบายการใช้ฟังก์ชัน NTH_VALUE() ด้วย คงไม่เสียหายที่จะพูดถึงทางเลือกอื่นของฟังก์ชันนี้ เช่น ฟังก์ชัน FIRST_VALUE() และ LAST_VALUE() บริษัทมักชอบวัดค่าควอไทล์ ควอนไทล์ และเปอร์เซ็นไทล์โดยทั่วไป ในการดำเนินการนี้ นักวิทยาศาสตร์ด้านข้อมูลต้องรู้วิธีใช้ฟังก์ชันหน้าต่าง NTILE() ด้วยเช่นกัน

ใน SQL มักจะมีหลายวิธีในการเข้าถึงงาน ยังคง ฟังก์ชันของหน้าต่างให้วิธีที่ง่ายที่สุดในการดำเนินการทั่วไป แต่ซับซ้อน ตัวอย่างที่ดีของฟังก์ชันหน้าต่างดังกล่าวคือ LAG() หรือ LEAD() ดังนั้น คุณจึงควรทำความคุ้นเคยกับฟังก์ชันเหล่านี้เช่นกัน ตัวอย่างเช่น มาดูตัวอย่างจากวิธีแก้ปัญหาก่อนหน้านี้กับคำถามสัมภาษณ์ Airbnb ที่ยาก:

เพื่อแสดงจำนวนโฮสต์ในปีที่แล้ว เราใช้ฟังก์ชัน LAG() กับคำสั่ง OVER สิ่งนี้สามารถทำได้หลายวิธี แต่ฟังก์ชั่นหน้าต่างทำให้เราได้ผลลัพธ์ที่ต้องการในโค้ด SQL เพียงบรรทัดเดียว:

LAG(current_year_host, 1) OVER (ORDER BY year) เป็น prev_year_host

บริษัทจำนวนมากจำเป็นต้องคำนวณการเติบโตในช่วงเวลาหนึ่ง ฟังก์ชัน LAG() มีค่ามากสำหรับการทำงานที่ได้รับมอบหมายดังกล่าว
 
 

แนวคิดขั้นสูง N3: การเติบโตทุกเดือน

หลายองค์กรใช้การวิเคราะห์ข้อมูลเพื่อวัดผลการปฏิบัติงานของตนเอง ซึ่งอาจนำมาซึ่งการวัดประสิทธิภาพของแคมเปญการตลาดหรือ ROI จากการลงทุนเฉพาะ การวิเคราะห์ดังกล่าวต้องใช้ความรู้เชิงลึกเกี่ยวกับ SQL เช่น วันที่ เวลา และฟังก์ชันของหน้าต่าง

นักวิทยาศาสตร์ด้านข้อมูลจะต้องพิสูจน์ทักษะของพวกเขาในการจัดรูปแบบข้อมูลและแสดงเป็นเปอร์เซ็นต์หรือในรูปแบบอื่นใด โดยทั่วไป ในการแก้ปัญหาเชิงปฏิบัติที่คุณต้องคำนวณการเติบโตแบบเดือนต่อเดือน คุณต้องใช้ชุดทักษะหลายชุดร่วมกัน แนวคิดที่จำเป็นบางอย่างจะเป็นขั้นสูง (ฟังก์ชันหน้าต่าง การจัดการวันที่-เวลา) ในขณะที่แนวคิดอื่นๆ จะเป็นแบบพื้นฐาน (ฟังก์ชันรวมและคำสั่ง SQL ทั่วไป)

มาดูตัวอย่างคำถามโดยผู้สัมภาษณ์ที่ Amazon

หลักฐาน:

 
ในคำถามนี้ เราต้องทำงานกับตารางการซื้อและคำนวณการเติบโตรายเดือนหรือรายได้ที่ลดลง ผลลัพธ์สุดท้ายต้องอยู่ในรูปแบบเฉพาะ (รูปแบบ ปปปป-ดด) และเปอร์เซ็นต์ควรถูกปัดเศษให้เป็นทศนิยมที่ใกล้ที่สุดอันดับสอง 

วิธีการแก้:

 
เมื่อทำงานในลักษณะนี้ สิ่งแรกที่คุณต้องทำคือเข้าใจตาราง คุณควรระบุคอลัมน์ที่ต้องทำงานด้วยเพื่อตอบคำถาม และผลลัพธ์ของคุณจะเป็นอย่างไร

ในตัวอย่างของเรา ค่าข้อมูลมีประเภทวัตถุ ดังนั้นเราจะต้องใช้ฟังก์ชัน CAST() เพื่อแปลงเป็นประเภทวันที่

SELECT to_char(cast(created_at as date), 'YYYY-MM') จาก sf_transactions

คำถามยังระบุรูปแบบสำหรับวันที่ ดังนั้นเราจึงสามารถใช้ฟังก์ชันTO_CHAR() ใน SQL เพื่อส่งออกวันที่ในรูปแบบนี้

ในการคำนวณการเติบโต เราควรเลือกฟังก์ชันการรวม created_at และ SUM() เพื่อรับปริมาณการขายทั้งหมดสำหรับวันนั้น 

SELECT to_char(cast(created_at as date), 'YYYY-MM'), created_at, sum(value) จาก sf_transactions

ณ จุดนี้ เราต้องใช้ฟังก์ชันหน้าต่างอีกครั้ง โดยเฉพาะอย่างยิ่ง เราจะใช้ฟังก์ชัน LAG() เพื่อเข้าถึงปริมาณของเดือนที่แล้วและแสดงเป็นคอลัมน์แยกต่างหาก ในการนั้น เราจำเป็นต้องมี OVER clause ด้วย

SELECT to_char(cast(created_at as date), 'YYYY-MM') AS year_month, created_at, sum(value) lag(sum(value), 1) OVER (ORDER BY created_at::date) จาก sf_transactions GROUP BY created_at

จากโค้ดที่เราเขียนไปแล้ว ตารางของเราจะมีลักษณะดังนี้:

ในที่นี้ เรามีวันที่และค่ารวมที่เกี่ยวข้องในคอลัมน์ผลรวม และค่าของวันที่ล่าสุดในคอลัมน์ความล่าช้า ตอนนี้ เราสามารถแทนค่าลงในสูตรและแสดงอัตราการเติบโตในคอลัมน์แยกต่างหาก

เราควรลบคอลัมน์ created_at ที่ไม่จำเป็นออก และเปลี่ยนส่วนคำสั่ง GROUP BY และ ORDER BY เป็น year_month

SELECT to_char(cast(created_at as date), 'YYYY-MM') AS year_month, sum(value), lag(sum(value), 1) OVER (ORDER BY to_char(cast(created_at as date)) จาก sf_transactions GROUP BY ปี_เดือน

เมื่อเรารันโค้ด ตารางของเราควรมีเฉพาะคอลัมน์ที่จำเป็นสำหรับการคำนวณของเรา

ในที่สุดเราก็มาถึงทางออกได้แล้ว นี่คือลักษณะของรหัสสุดท้าย:

SELECT to_char(created_at::date, 'YYYY-MM') AS year_month, round(((ผลรวม(value)) - lag(sum(value), 1) OVER w) / (lag(sum(value), 1) OVER w)) * 100, 2) AS Revenue_diff_pct จาก sf_transactions GROUP BY year_month WINDOW w AS ( ORDER BY to_char(created_at::date, 'YYYY-MM')) เรียงลำดับตาม year_month ASC

ในโค้ดนี้ เราใช้ค่าคอลัมน์สองค่าจากตัวอย่างก่อนหน้าและคำนวณความแตกต่างระหว่างค่าเหล่านี้ โปรดทราบว่าเรายังใช้ชื่อแทนหน้าต่างเพื่อลดความซ้ำซ้อนของโค้ดของเรา

จากนั้น ตามอัลกอริทึม เราจะหารด้วยรายได้ของเดือนปัจจุบันแล้วคูณด้วย 100 เพื่อให้ได้ค่าเปอร์เซ็นต์ สุดท้าย เราปัดเศษค่าเปอร์เซ็นต์เป็นทศนิยมสองจุด เรามาถึงคำตอบที่ตรงตามความต้องการทั้งหมดของงาน 

แนวคิดขั้นสูง N4: อัตราการเลิกเรียน

 
แม้ว่าจะตรงกันข้ามกับการเติบโต แต่ความปั่นป่วนก็เป็นตัวชี้วัดที่สำคัญเช่นกัน หลายบริษัทติดตามอัตราการเลิกใช้งาน โดยเฉพาะอย่างยิ่งหากรูปแบบธุรกิจของพวกเขาเป็นแบบสมัครสมาชิก ด้วยวิธีนี้ พวกเขาสามารถติดตามจำนวนการสมัครรับข้อมูลหรือบัญชีที่สูญหาย และคาดการณ์สาเหตุที่ทำให้เกิด นักวิทยาศาสตร์ข้อมูลที่มีประสบการณ์จะต้องรู้ว่าฟังก์ชัน คำสั่ง และอนุประโยคใดที่จะใช้ในการคำนวณอัตราการเลิกใช้งาน

ข้อมูลการสมัครเป็นส่วนตัวมากและมีข้อมูลผู้ใช้ที่เป็นส่วนตัว นอกจากนี้ยังเป็นสิ่งสำคัญสำหรับนักวิทยาศาสตร์ด้านข้อมูลที่จะต้องทราบวิธีการทำงานกับข้อมูลดังกล่าวโดยไม่เปิดเผย การคำนวณอัตราการหยุดทำงานมักเกี่ยวข้องกับนิพจน์ตารางทั่วไป ซึ่งเป็นแนวคิดที่ค่อนข้างใหม่ นักวิทยาศาสตร์ข้อมูลที่ดีที่สุดควรรู้ว่าทำไม CTE จึงมีประโยชน์และเมื่อใดควรใช้ CTE เมื่อทำงานกับฐานข้อมูลเก่าที่ไม่มี CTE ผู้สมัครในอุดมคติควรจะสามารถทำงานให้เสร็จได้

นี่คือตัวอย่างงานยาก. ผู้สมัครสอบสัมภาษณ์ที่ Lyft จะได้รับงานนี้เพื่อคำนวณอัตราการเลิกจ้างของผู้ขับขี่ที่บริษัท

ในการแก้ปัญหานี้ นักวิทยาศาสตร์ด้านข้อมูลต้องใช้คำสั่ง case/ when ฟังก์ชันของหน้าต่าง เช่น LAG() เช่นเดียวกับ FROM / WHERE และอนุประโยคพื้นฐานอื่นๆ 

สรุป

 
การทำงานเป็นนักวิทยาศาสตร์ข้อมูลเป็นเวลาหลายปีนั้นดูน่าประทับใจในประวัติย่อ และจะทำให้คุณได้รับการสัมภาษณ์มากมาย อย่างไรก็ตาม เมื่อคุณก้าวเข้ามาแล้ว คุณยังต้องแสดงความรู้เพื่อเสริมประสบการณ์หลายปี แม้ว่าคุณจะมีประสบการณ์มากมายใน การเขียนแบบสอบถามในSQL, ไม่เจ็บที่จะใช้ทรัพยากรเช่น StrataScratch เพื่อฟื้นฟูความรู้ของคุณ

 
 
เนท โรซิดิ เป็นนักวิทยาศาสตร์ข้อมูลและในกลยุทธ์ผลิตภัณฑ์ เขายังเป็นผู้ช่วยศาสตราจารย์สอนการวิเคราะห์และเป็นผู้ก่อตั้ง StrataScratchซึ่งเป็นแพลตฟอร์มที่ช่วยให้นักวิทยาศาสตร์ด้านข้อมูลเตรียมพร้อมสำหรับการสัมภาษณ์ด้วยคำถามสัมภาษณ์จริงจากบริษัทชั้นนำ เชื่อมต่อกับเขาที่ ทวิตเตอร์: StrataScratch or LinkedIn.

ที่มา: https://www.kdnuggets.com/2022/01/sql-interview-questions-experienced-professionals.html