3
מרץ 2022
דודי לרך

רוצים להתאמן על מבחן SQL
אמיתי מראיון עבודה?

חשוב לזכור כמה כללים לפני שמתחילים

יש שני סוגי מבחנים בראיונות עבודה או יותר נכון שתי אסכולות:

1.מבחן ארוך שבדרך כלל יבדוק יותר טכניות ופחות חשיבה מחוץ לקופסה,
מבחן מסוג כזה יבדוק פונקציות מוכרות וטכניקות פשוטות GROUP BY , WHERE , WINDOWS FUNCTION.

 זמן המבחן יאפשר לכם לחשוב באריכות על התשובה והמטרה היא שבסוף המבחן תמלאו את כל השאלות בהצלחה, לרוב יתבטא במבחן ביתי.


2. מבחן קצר בין רבע שעה ל45 דקות בו תצטרכו לענות בצורה מהירה מאוד כמעין ״שליפה מהירה״ על השאלות תוך התמקדות ברעיון ובלוגיקה שבה ה SQL בנוי , במבחן מסוג זה תצטרכו להראות טכניקות מתוחכמות.

                            SELF JOIN , ROW_NUMBER,FIRST_VALUE()

חשוב לזכור שככל שתתרגלו יותר מבחנים ככה תגיעו יותר מוכנים למבחן, חשוב שתגיעו מוכנים כך שהרקע הטכני לא יהיה מכשול בשום צורה, אם אתם מעוניינים לתרגל מבחנים עם פתרונות מלאים בווידאו אתם מוזמנים להיכנס לקורס שלי, אם אתם מעוניינים לתרגל את ה SQL שלכם אתם יכולים להיכנס לקורס של חמישים שאלות ב SQL

שאלות

מנהל אתר ecommerce מצליח מעוניין לקבל כמה אינדיקציות בעקבות שינויים שבוצעו באתר.

לפנייך שתי טבלאות:

בטבלה ראשונה תמצא:

  • רשימת לקוחות
  • תאריך הצטרפות
  • , מדינה
  • כמות הפעמים שהלקוח עשה לייק
מבחן sql מלא צפייה עכשיו ב89 ש״ח
SAVED_ITEMS REGISTRATION_DATE COUNTRY USER_ID
1 1/1/21 USA 54363
3 2/3/21 UK 65389
5 3/2/21 ISRAEL 89745

בטבלה השנייה תמצא: רשימות לקוחות והכנסוות מלקוחות אלו

USER_ID REVENUE
89745 100$
65389 150$

 לפני שאתם נגשים לפתור את השאלות אני ממליץ לכם לכתוב את השאילתא באמצעות שימוש בדאטה בייס bigquery,

 היופי פה זה שהכנתי עבורכם מדריך איך בחמש דקות ללא צורך בכרטיס אשראי, יהיה לכם דאטה בייס אמיתי ותוכלו לתרגל את השאלות שכאן מתחת
 

שאלות

  1. רשום שאילתא שמחשבת לפי מדינה את כמות הלייקים וההכנסות ללקוחות שהוציאו מעל 140?
  2. חשב לפי מדינה את כמות הלייקים מתוך לקוחות שלא שילמו?
שאלות מסוג זה צריכות לקחת לכם לא יותר מעשר דקות כולל כתיבת השאילתא, מוכנים? לפתוח שעונים בהצלחה!

תשובות

				
					<!--QUESTION 1--> 
SELECT country
       ,SUM(likes) as likes
       .SUM(revenue) as revenue
    FROM table1
    LEFT JOIN table2
    ON table1.user_id = table2.user_id
        WHERE revenue >140
    GROUP BY 1
				
			
				
					<!--QUESTION 2-->
SELECT country
       ,SUM(likes) as likes
    FROM table1
    LEFT JOIN table2
    ON table1.user_id = table2.user_id
        WHERE revenue IS NULL
    GROUP BY 1

 

שאלה

1.לפניך טבלה ובה רכישות של לקוחות לפי תאריך ושם מוצר
User_id Purchase_time Drink
2749 23/02/2018 11:00 cola
2749 23/02/2018 12:00 pepsi
2749 23/02/2018 11:00 cola
3334 23/02/2018 17:00 pepsi
3334 23/02/2018 11:00 cola
  • חשב את המשקה הראשון שכל יוזר קנה ואת המשקה האחרון שקנה?
    כך שתוצר טבלה בתצורה הזאת:
User_id status drink
2749 first cola
2749 last pepsi
3177 first cola
3177 last pepsi

תשובה

				
					SELECT 'first' AS status,
        user_id,
        status
    FROM
        (
         SELECT *,ROW_NUMBER() OVER (PARTITION BY user_id order by purchase_time ) as rn 
            FROM
        ) 
        WHERE rn = 1
UNION ALL
SELECT 'last' AS status,
        user_id,
        status
    FROM
        (
         SELECT *,ROW_NUMBER() OVER (PARTITION BY user_id order by purchase_time DESC) as rn 
            FROM
        ) 
        WHERE rn = 1
ORDER BY user_id

 

שאלה

 
לאחרונה יש ירידות מרכזיות בהכנסות החברה,
מנהל הדאטה רוצה להבין את השינוי בביצועי החברה, טרנד יומי לא מספק לו את ההבנה והוא מעוניין השוואה של אותו יום בשבוע קודם (לימים יש חשיבות משמעותית מבחינת ביצועי החברה, ימי שני שלישי ורביעי חזקים וראשון ושבת חלשים)

לפניך טבלה עם ביצועי החברה  בשבועיים האחרונים ברמת היום

 
  • צור עמודה נוספת שמראה את האחוז שינוי מהיום הקודם מאותו שבוע?
    דוגמא:
    בתאריך 17/7/2022  ההכנסות היו 110 לעומת 120 אחוז שינוי של -8%
 
table name = t1
 
עליך לענות על השאלה בשתי דרכים שונות!
day_week date revenue
1 10/07/2022 120
2 11/07/2022 180
3 12/07/2022 200
4 13/07/2022 220
5 14/07/2022 240
6 15/07/2022 230
7 16/07/2022 100
1 17/07/2022 110
2 18/07/2022 166
3 19/07/2022 184
4 20/07/2022 202
5 21/07/2022 221
6 22/07/2022 212
7 23/07/2022 92

תשובה

				
					/* ANSWER ONE */
select t1.day_week,
	   t1.date,
	   t1.revenue
	   safe_divide(t2.revenue,t1.revenue) - 1
	from t1
	left join t1 as t2 
	on t1.date - 7 = t2.date 
	


/* ANSWER TWO */

select t1.date
      ,t1.orders
      ,lag(t1.orders,7) over (order by date) seven_days_orders
 from t1
order by 1

 

שאלה

 לפניך הטבלה הבאה הנח שבטבלה יש 20 שורות
Order ID Customer ID Order Date Order Total
1001 101 2023-03-30 $124.50
1002 102 2023-03-31 $78.95
1003 103 2023-04-01 $175.20
1004 104 2023-04-02 $42.10
1005 105 2023-04-03 $99.99

עליך למצוא את חמשת הלקוחות שהוציאו הכי הרבה כסף בחודש האחרון , פתור בשתי דרכים שונות

				
					SELECT *
FROM UNNEST([
  STRUCT<
    order_id STRING,
    customer_id STRING,
    order_date DATE,
    order_total NUMERIC
  > ('1001', '101', DATE '2023-03-30', 124.50),
  ('1002', '102', DATE '2023-03-31', 78.95),
  ('1003', '103', DATE '2023-04-01', 175.20),
  ('1004', '104', DATE '2023-04-02', 42.10),
  ('1005', '105', DATE '2023-04-03', 99.99),
  ('1006', '106', DATE '2023-04-04', 225.75),
  ('1007', '107', DATE '2023-04-05', 10.50),
  ('1008', '108', DATE '2023-04-06', 54.20),
  ('1009', '109', DATE '2023-04-07', 185.90),
  ('1010', '110', DATE '2023-04-08', 320.00),
  ('1011', '111', DATE '2023-04-09', 75.50),
  ('1012', '112', DATE '2023-04-10', 92.10),
  ('1013', '113', DATE '2023-04-11', 125.00),
  ('1014', '114', DATE '2023-04-12', 500.00),
  ('1015', '115', DATE '2023-04-13', 12.99)])

תשובה

				
					--option 1--
with orders as 
(
SELECT *
FROM UNNEST([
  STRUCT<
    order_id STRING,
    customer_id STRING,
    order_date DATE,
    order_total NUMERIC
  > ('1001', '101', DATE '2023-03-30', 124.50),
  ('1002', '102', DATE '2023-03-31', 78.95),
  ('1003', '103', DATE '2023-04-01', 175.20),
  ('1004', '104', DATE '2023-04-02', 42.10),
  ('1005', '105', DATE '2023-04-03', 99.99),
  ('1006', '106', DATE '2023-04-04', 225.75),
  ('1007', '107', DATE '2023-04-05', 10.50),
  ('1008', '108', DATE '2023-04-06', 54.20),
  ('1009', '109', DATE '2023-04-07', 185.90),
  ('1010', '110', DATE '2023-04-08', 320.00),
  ('1011', '111', DATE '2023-04-09', 75.50),
  ('1012', '112', DATE '2023-04-10', 92.10),
  ('1013', '113', DATE '2023-04-11', 125.00),
  ('1014', '114', DATE '2023-04-12', 500.00),
  ('1015', '115', DATE '2023-04-13', 12.99)])
)

SELECT customer_id, SUM(order_total) AS total_spent
FROM orders
WHERE order_date >= DATE_SUB(current_date(), INTERVAL 1 MONTH)
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 5;

				
			
				
					--option 2--
with orders as 
(
SELECT *
FROM UNNEST([
  STRUCT<
    order_id STRING,
    customer_id STRING,
    order_date DATE,
    order_total NUMERIC
  > ('1001', '101', DATE '2023-03-30', 124.50),
  ('1002', '102', DATE '2023-03-31', 78.95),
  ('1003', '103', DATE '2023-04-01', 175.20),
  ('1004', '104', DATE '2023-04-02', 42.10),
  ('1005', '105', DATE '2023-04-03', 99.99),
  ('1006', '106', DATE '2023-04-04', 225.75),
  ('1007', '107', DATE '2023-04-05', 10.50),
  ('1008', '108', DATE '2023-04-06', 54.20),
  ('1009', '109', DATE '2023-04-07', 185.90),
  ('1010', '110', DATE '2023-04-08', 320.00),
  ('1011', '111', DATE '2023-04-09', 75.50),
  ('1012', '112', DATE '2023-04-10', 92.10),
  ('1013', '113', DATE '2023-04-11', 125.00),
  ('1014', '114', DATE '2023-04-12', 500.00),
  ('1015', '115', DATE '2023-04-13', 12.99)])
)

SELECT customer_id, total_spent
FROM (
    SELECT customer_id, SUM(order_total) AS total_spent,
           RANK() OVER (ORDER BY SUM(order_total) DESC) AS rank
    FROM orders
    WHERE order_date >= DATE_SUB(current_date(), INTERVAL 1 MONTH)
    GROUP BY customer_id
) AS ranked_orders
WHERE rank <= 5;

רוצים להמשיך להתאמן מוזמנים להמשיך להתאמן עם הקורסים שלי

רוצים לתרגל ביגקוורי בחינם ללא צורך בכרטיס אשראי?

הדרך הכי אפקטיבית שאני יכול להמליץ לכם ללמוד איתה sql היא תוך כדי תרגול אל תפספסו את התפקיד הבא כי לא התכוננתם כמו שצריך – מבחנים אמיתיים עם דוגמאות מלאות 

מבחנים לראיונות עבודה ב SQL

מבחני מיון ב SQL לראיונות עבודה,
להתקבל למקומות העבודה
הכי נחשקים במשק,
לתפקיד Data analyst/Product analyst/Business analyst

עכשיו במבצע