TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 06

Scalar Functions: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

STRING & DATE - CONCEPTUAL

  1. Q1Difference between UPPER, LOWER, and INITCAP - give one example each.
  2. Q2What does LENGTH return for a NULL value?
  3. Q3When would you use SUBSTRING vs SPLIT_PART?
  4. Q4Difference between || (concat operator) and CONCAT function for NULL handling.
  5. Q5What does TRIM remove by default?
  6. Q6Difference between NOW() and CURRENT_DATE.
  7. Q7Why does subtracting two dates give an "interval" - and how do you convert it to integer days?
  8. Q8When would you use DATE_TRUNC vs EXTRACT?
  9. Q9Why does TO_CHAR(date, 'Day') give a name padded with spaces - and how do you fix it?
  10. Q10Does AGE() with one argument compare to TODAY, or another date?

STRING FUNCTIONS ON V3 DATA

  1. Q11The Customer Service team wants all customer names in UPPERCASE. Show customer_id, first_name, last_name in UPPER case.
  2. Q12Show customer first_name in lower case.
  3. Q13Show customer full_name using INITCAP and concatenation: 'firstname lastname' (proper case).
  4. Q14Show product_name as UPPER from products.products.
  5. Q15Show LENGTH of every product_name (number of characters).
  6. Q16Show the LENGTH of every customer's email.
  7. Q17Extract the FIRST 5 characters of product_name using LEFT.
  8. Q18Extract the LAST 4 characters of every customer phone using RIGHT (the masking pattern).
  9. Q19Use SUBSTRING to grab characters 1 to 3 of product_name.
  10. Q20Use SPLIT_PART to extract the domain of every customer email (the part after '@').
  11. Q21Use SPLIT_PART to extract the username of every customer email (the part before '@').
  12. Q22Find the POSITION of '@' in every customer email.
  13. Q23Show product_name with all double spaces replaced by single space using REPLACE.
  14. Q24Use TRIM to clean up whitespace around support ticket subjects.
  15. Q25Concatenate first_name and last_name with a space between, alias as full_name (customers.customers).
  16. Q26The CHRO wants every employee's full name + role joined as 'Aarav Sharma - Store Manager'.
  17. Q27Show every customer's email in lower case (data hygiene step).
  18. Q28Concatenate 'Order #' with order_id (cast as text) for a friendly display.
  19. Q29Build a customer code like 'CUST-' || customer_id for ticket templates.
  20. Q30For products, build a display string 'PRODUCT: ' || product_name.
  21. Q31Show employees where role contains 'Manager' (LIKE).
  22. Q32Show only the part of product_name AFTER the first space (use POSITION + SUBSTRING).
  23. Q33Show only the FIRST WORD of every product_name using SPLIT_PART.
  24. Q34Mask every customer email: keep first 2 chars, then 'XXX', then domain. (e.g., 'pr***@gmail.com').
  25. Q35Show every customer's first_name with the length next to it.
  26. Q36UPPER + TRIM combo - clean up support ticket subjects.
  27. Q37Show the role of every employee with leading/trailing spaces removed.
  28. Q38From customers.customers email, get domain only - useful for domain-distribution reports later.
  29. Q39Concatenate city + state for stores.stores: 'city, state' format (note: stores.stores doesn't have state - use 'India' as the literal second part).
  30. Q40Build a 'Hi <first_name>!' greeting string per customer for the email campaign.

DATE FUNCTIONS ON V3 DATA

  1. Q41Show the current date (server side).
  2. Q42Show the current timestamp with timezone.
  3. Q43Show customer_id and registration_date EXTRACT(YEAR) - what year did they register?
  4. Q44Show order_id and EXTRACT(MONTH FROM order_date) for each order.
  5. Q45Show order_id and EXTRACT(DAY FROM order_date) per order.
  6. Q46Show order_id and EXTRACT(DOW FROM order_date) - day-of-week number.
  7. Q47Show order_id and EXTRACT(QUARTER FROM order_date).
  8. Q48Bucket each order to the first day of its month using DATE_TRUNC.
  9. Q49Bucket each order to its week using DATE_TRUNC('week', order_date).
  10. Q50Bucket each order to its year using DATE_TRUNC.
  11. Q51Show every order_date formatted as 'DD-Mon-YYYY' using TO_CHAR.
  12. Q52Show every registration_date formatted as 'YYYY-MM-DD' explicitly.
  13. Q53Show day-of-week NAME for each order_date using TO_CHAR(order_date, 'FMDay').
  14. Q54Show month NAME for each order_date using TO_CHAR(order_date, 'FMMonth').
  15. Q55How many DAYS ago was each order placed? (CURRENT_DATE - order_date).
  16. Q56Use AGE() to show each customer's account age.
  17. Q57Extract just the years portion of each customer's account age.
  18. Q58Compute "days since shipment" for sales.shipments where shipped_date IS NOT NULL.
  19. Q59Compute delivery duration (delivered_date - shipped_date) for delivered shipments.
  20. Q60Compute ticket resolution time (resolved_date - created_date) for resolved tickets.
  21. Q61Show only orders placed in the LAST 30 days (use INTERVAL).
  22. Q62Show orders placed in the LAST 7 days.
  23. Q63Show customers registered in the LAST 90 days.
  24. Q64Show tickets created in the LAST 24 hours.
  25. Q65Use DATE_TRUNC and TO_CHAR together to label each order's month like 'Mar 2025'.
  26. Q66Show shipments where shipped_date is in calendar year 2025.
  27. Q67Show calls from call_center.calls that started in January 2025.
  28. Q68Show hr.attendance for any Saturday (EXTRACT(DOW) = 6).
  29. Q69Show every payroll pay_slip where the payment_date falls in Q1 of 2025.
  30. Q70Compute the customer's tenure in YEARS only: EXTRACT(YEAR FROM AGE(registration_date)).

MIXED STRING + DATE

  1. Q71Build a label per order: '#' || order_id || ' on ' || TO_CHAR(order_date, 'DD-Mon-YYYY').
  2. Q72Show customer full name (INITCAP) + 'registered on ' + formatted registration_date.
  3. Q73Show employee full name + 'joined on ' + TO_CHAR(joining_date, 'Mon YYYY').
  4. Q74Build a product label: UPPER(product_name) || ' - Rs' || price for the price catalog.
  5. Q75Build a ticket label: subject (trimmed + INITCAP) + ' (created ' + TO_CHAR(created_date, 'DD-Mon HH24:MI') + ')'.
  6. Q76Show stores.stores with store_name (INITCAP) + ' in ' + city.
  7. Q77Build an employee greeting: 'Hi ' || INITCAP(first_name) || ', you joined us ' || EXTRACT(YEAR FROM AGE(joining_date)) || ' years ago.'.
  8. Q78Make a customer thank-you string: 'Thank you ' || INITCAP(first_name) || '! Member since ' || EXTRACT(YEAR FROM registration_date).
  9. Q79Build an order display: 'Order ' || order_id || ' placed on ' || TO_CHAR(order_date, 'FMDay, DD Mon YYYY').
  10. Q80Customer tenure bucket: TO_CHAR(EXTRACT(YEAR FROM AGE(registration_date)), 'FM00') || ' years'.
  11. Q81Build a shipment label: courier_name (UPPER) + ' - ' + TO_CHAR(shipped_date, 'DD/MM/YYYY') for sales.shipments.
  12. Q82Build a call duration label: 'Call ' || call_id || ': ' || call_duration_seconds || 's'.
  13. Q83Format each campaign with name (INITCAP) and budget (TO_CHAR(budget, 'FM99,99,999')).
  14. Q84Show every customer's email in lower case + their domain (SPLIT_PART) + length of email.
  15. Q85Show product_name + length-of-name + first-3-chars.
  16. Q86Build an employee tenure display: 'EMP-' || employee_id::TEXT || ': ' || first_name || ' (' || EXTRACT(YEAR FROM AGE(joining_date)) || ' yrs)'.
  17. Q87Show every order_id with its month-name + year: '#' || order_id || ' in ' || TO_CHAR(order_date, 'FMMonth YYYY').
  18. Q88Trim+INITCAP the ticket subject and prefix with 'TICKET-' || ticket_id.
  19. Q89Build a 'days since last activity' indicator for every shipment: CURRENT_DATE - shipped_date with a ' days ago' suffix.
  20. Q90Show every customer's account age in 'Y years M months' format using AGE + EXTRACT.
  21. Q91Concat region info: 'Order from store ' || store_id || ' on ' || TO_CHAR(order_date, 'YYYY-MM-DD').
  22. Q92For every page_view, build a session label: 'Session ' || session_id || ' viewed ' || page_url + ' at ' + TO_CHAR(view_timestamp, 'HH24:MI:SS').
  23. Q93For every call_center.calls row, build a string 'Call #' || call_id || ' by agent ' || agent_id || ' at ' || TO_CHAR(call_start_time, 'DD-Mon HH24:MI').
  24. Q94Show every loyalty member with their tier_id and 'joined ' || EXTRACT(YEAR FROM AGE(join_date)) || ' years ago'.
  25. Q95Build a customer display: customer_id, INITCAP(first_name || ' ' || last_name), and EXTRACT(YEAR FROM AGE(registration_date)) || ' yrs as customer'.
  26. Q96For each work order, show 'WO-' || work_order_id || ' produced ' || quantity_produced || ' units on ' || TO_CHAR(end_timestamp, 'DD-Mon-YYYY').
  27. Q97For each expense, show 'Exp #' || expense_id || ' - Rs' || amount || ' on ' || TO_CHAR(expense_date, 'DD-Mon-YYYY').
  28. Q98For every customer review, show 'REVIEW ' || review_id || ': ' || rating || '* on ' || TO_CHAR(review_date, 'DD-Mon-YYYY').
  29. Q99Show every API request: status_code || ' ' || method || ' ' || endpoint + ' at ' + TO_CHAR(timestamp, 'HH24:MI:SS').
  30. Q100Build a 'days remaining in current month' utility: (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' - 1 day) - CURRENT_DATE.

Combined ideas, multi-step thinking

STRING / DATE DEEPER CONCEPTUAL

  1. Q1What does NULL || 'abc' return - and how do you avoid the NULL trap?
  2. Q2Difference between SUBSTRING and SUBSTR in PostgreSQL.
  3. Q3Compare LIKE '%abc%' vs the regex operator ~ 'abc' - which is case-insensitive?
  4. Q4Explain LATERAL vs subqueries in the context of expanding REGEXP_MATCHES result.
  5. Q5What does TO_CHAR(num, '99G99G999') do - what's the G?
  6. Q6Compare TO_CHAR(date, 'Mon') vs 'FMMon' - what's the difference in output?
  7. Q7Why is EXTRACT(EPOCH FROM ...) needed when AGE() returns an interval?
  8. Q8What does JUSTIFY_INTERVAL do?
  9. Q9Compare TO_DATE('2025-01-15', 'YYYY-MM-DD') vs '2025-01-15'::DATE.
  10. Q10Why does NOW() inside a transaction always return the SAME time?
  11. Q11Explain CURRENT_TIMESTAMP vs STATEMENT_TIMESTAMP vs CLOCK_TIMESTAMP.
  12. Q12What's the difference between AGE(d1) and AGE(d1, d2)?
  13. Q13Why is DATE_TRUNC('week', date) potentially confusing for Indian users (week starts when?).
  14. Q14Compare INTERVAL '1 month' vs adding 30 days - when do they differ?
  15. Q15What does the TIMEZONE function do - give a use case.
  16. Q16Explain LPAD and RPAD with one example each.
  17. Q17What's the difference between OVERLAY and SUBSTRING?
  18. Q18What does REGEXP_REPLACE do - give one cleaning use case.
  19. Q19Compare REGEXP_MATCHES (returns text[]) vs REGEXP_MATCH (returns text).
  20. Q20When would you use REGEXP_SPLIT_TO_TABLE vs REGEXP_SPLIT_TO_ARRAY?
  21. Q21Why is STRPOS(text, substring) sometimes preferred over POSITION(substring IN text)?
  22. Q22Explain what FORMAT() does - give a SQL injection-safe example.
  23. Q23Difference between TO_NUMBER('1,234.50', '9,999.99') and '1234.50'::NUMERIC.
  24. Q24Why does LENGTH on a TEXT with multi-byte characters return character count, not bytes - and where's the byte-count?
  25. Q25How do you find the WEEK NUMBER of a date in PostgreSQL? What's the difference between 'WW' and 'IW'?

NESTED STRING FUNCTIONS

  1. Q26Show customer's full_name with first character of each part capitalized + collapse multiple spaces (nested INITCAP + REGEXP_REPLACE).
  2. Q27Mask email middle: '[email protected]' style - keep first 2, mask middle, keep '@domain'.
  3. Q28Standardize phone numbers: strip spaces, dashes, '+91 ' prefix.
  4. Q29Extract username AND domain from email as TWO columns.
  5. Q30Format product_name as TITLE Case with no trailing whitespace.
  6. Q31Combine first_name + last_name into a single email-username pattern: 'first.last'.
  7. Q32Build an SKU-like code from product_name: UPPER(LEFT(product_name, 3)) || '-' || product_id::TEXT.
  8. Q33Remove all non-digit characters from phone using REGEXP_REPLACE.
  9. Q34Remove HTML tags from a ticket subject (if any) using REGEXP_REPLACE.
  10. Q35Count vowels in a customer's first_name.
  11. Q36Reverse a string: use REVERSE(text).
  12. Q37Determine if a customer's email is from a "free" provider (gmail/yahoo/outlook).
  13. Q38Build a name initials display from first_name + last_name: 'P.S.' style.
  14. Q39Truncate a long support ticket subject to 50 chars + '...' if longer.
  15. Q40Build a slug from product_name: lowercase, replace non-alphanumeric with '-'.
  16. Q41Find products whose name has more than 30 characters using LENGTH.
  17. Q42Find customers whose first_name and last_name are exact duplicates (case-insensitive).
  18. Q43Show product_name with every '-' replaced by ' ' and the result INITCAP'd.
  19. Q44Strip a country-code prefix from phone: if it starts with '+91 ', remove it.
  20. Q45Generate a 10-character random-looking suffix per product (use MD5 + LEFT).
  21. Q46Build a 6-char short_code from full email using MD5: LEFT(MD5(email), 6).
  22. Q47Validate (via LIKE / regex) every email contains exactly ONE '@'.
  23. Q48Find customer first_names that contain non-ASCII characters.
  24. Q49Extract the area-code from a phone string assumed to be '+91 9876543210': characters 5-7.
  25. Q50Build a CSV-style "Last, First" display from first_name + last_name.

COMPLEX DATE MATH

  1. Q51Show every customer's tenure in 'Y years M months D days' format.
  2. Q52Compute the LAST day of the month of each order_date.
  3. Q53Compute the FIRST day of the QUARTER for each order.
  4. Q54Show day-of-year (1-365) for each order.
  5. Q55Show week-of-year (1-52) using ISO week number for each order.
  6. Q56Compute "days until next renewal" for each loyalty member assuming renewal_date = join_date + 1 year.
  7. Q57Show every shipment's delivery delay (delivered - shipped) in DAYS as a numeric.
  8. Q58Show every ticket's resolution time in HOURS as a numeric.
  9. Q59Show employees with their tenure bucketed: < 1 yr 'New', < 3 yrs 'Mid', else 'Veteran'.
  10. Q60Show every order placed at month-end (last 3 days of the month).
  11. Q61Show every order placed on a Friday.
  12. Q62Show every employee who joined on the FIRST of any month.
  13. Q63Show every customer registered exactly N years ago today (anniversary report).
  14. Q64Convert an attendance check_in (TIMESTAMP) to "minutes after midnight".
  15. Q65Bucket api_requests by hour-of-day and count per hour (just GROUP BY hour).
  16. Q66Find the SAME date a year ago for each order_date (date - INTERVAL '1 year').
  17. Q67Compute working days between shipped_date and delivered_date - approximate using day-of-week (subtract weekends).
  18. Q68Show every order_date formatted as 'Q1-2025', 'Q2-2025' etc.
  19. Q69Show every order placed in the LAST FISCAL YEAR (assume FY = April to March).
  20. Q70Compute days between two timestamps with millisecond precision.
  21. Q71Show the start of the ISO week for each order_date.
  22. Q72Build a list of all DATES in the most recent 30 days (use generate_series).
  23. Q73Find every TICKET resolved on a weekend.
  24. Q74Find any work_order with an end_timestamp earlier than its start_timestamp (data quality bug).
  25. Q75Compute how MANY days each order is from the median order date (advanced).

PRODUCTION PATTERNS - INVOICE/EMAIL/REPORT

  1. Q76Build a customer welcome email subject: 'Welcome ' || INITCAP(first_name) || '! Your member ID is CUST-' || customer_id.
  2. Q77Build an invoice header line: 'INVOICE #' || order_id || ' - ' || INITCAP(c.first_name || ' ' || c.last_name) || ' - ' || TO_CHAR(o.order_date, 'DD-Mon-YYYY').
  3. Q78Build a shipment status line: 'Shipment ' || shipment_id || ' from ' || INITCAP(courier_name) || ' - ' || CASE WHEN delivered_date IS NULL THEN 'IN TRANSIT (since ' || TO_CHAR(shipped_date, 'DD Mon') || ')' ELSE 'DELIVERED on ' || TO_CHAR(delivered_date, 'DD Mon') END.
  4. Q79Build a renewal reminder: 'Hi ' || first_name || ', your loyalty membership renews on ' || TO_CHAR(join_date + INTERVAL '1 year', 'DD Mon YYYY').
  5. Q80Build a ticket aging label: 'Open since ' || EXTRACT(DAY FROM CURRENT_TIMESTAMP - created_date) || ' days' for unresolved tickets.
  6. Q81Build a salary slip line: 'Net Rs' || TO_CHAR(net_salary, 'FM99,99,999.00') || ' for ' || salary_month || ' ' || salary_year.
  7. Q82Build a return notice: 'Return ' || return_id || ' for Rs' || TO_CHAR(refund_amount, 'FM99,99,999') || ' processed on ' || TO_CHAR(return_date, 'DD-Mon-YYYY').
  8. Q83Build a marketing campaign label: campaign_name || ' (Rs' || TO_CHAR(budget, 'FM99,99,999') || ', ' || TO_CHAR(start_date, 'Mon YYYY') || ')'.
  9. Q84Build an expense report line: 'Expense ' || expense_id || ': Rs' || TO_CHAR(amount, 'FM99,99,999.00') || ' on ' || TO_CHAR(expense_date, 'DD-Mon-YYYY') || ' - ' || description.
  10. Q85Build a customer activity stamp: 'Customer ' || customer_id || ' joined ' || EXTRACT(YEAR FROM AGE(registration_date)) || 'y ' || EXTRACT(MONTH FROM AGE(registration_date)) || 'm ago'.
  11. Q86Build a call log: 'Call ' || call_id || ' (' || call_duration_seconds || 's) at ' || TO_CHAR(call_start_time, 'DD-Mon HH24:MI').
  12. Q87Build a page-view event: 'PV-' || view_id || ': ' || page_url || ' on ' || device_type || ' (' || os || ')'.
  13. Q88Build a review summary: '*' || REPEAT('*', rating - 1) || ' on ' || TO_CHAR(review_date, 'DD Mon YYYY').
  14. Q89Build a work_order tag: 'WO#' || work_order_id || ' (' || quantity_produced || ' produced, ' || rejected_quantity || ' rejected)'.
  15. Q90Build a transcript snippet: LEFT(transcript_text, 100) || '...' if longer than 100 else transcript_text.
  16. Q91Build a search-friendly product slug for URL: LOWER(REGEXP_REPLACE(product_name, '[^a-zA-Z0-9]+', '-', 'g')) || '-' || product_id.
  17. Q92Build a customer JSON summary as text: '{"id":' || customer_id || ',"name":"' || first_name || '","tier":"' || tier || '"}'.
  18. Q93Build a 'last seen' label using AGE for each customer: based on most recent page_view. (Single-table tip: use a placeholder MAX timestamp.)
  19. Q94Build a 'days since hire' label per employee.
  20. Q95Build a fiscal-year label (FY24, FY25) for each order_date assuming FY = April-March.
  21. Q96Build a date-bucket label per order: 'today' / 'yesterday' / 'this_week' / 'this_month' / 'older'.
  22. Q97Build a 'shipped-but-not-delivered for X days' label per shipment.
  23. Q98Build a 'high-value' flag string per order: order_id + ' [HIGH]' if net_total > 10000 else order_id + ' [normal]'.
  24. Q99Build a sentiment summary per call: 'Call ' || call_id || ' - sentiment ' || CASE WHEN sentiment_score >= 0.5 THEN 'positive' WHEN sentiment_score <= -0.5 THEN 'negative' ELSE 'neutral' END (from transcripts joined to calls).
  25. Q100Build an aging report: every unresolved ticket with 'Ticket ' || ticket_id || ' open ' || (CURRENT_DATE - created_date::DATE) || ' days; priority ' || priority.

Interview grade, edge cases

SCALAR FUNCTIONS - CONCEPTUAL

  1. Q1What's the difference between LIKE, ILIKE, ~, ~*, ~~, ~~* in Postgres?
  2. Q2Compare regexp_match vs regexp_matches - which returns multiple?
  3. Q3Explain regexp_split_to_array vs string_to_array.
  4. Q4Why does \d work in Postgres regex but \d+ inside SQL string needs '\\d+'?
  5. Q5What is the SUBSTRING(... FROM regex) form - and how is it useful?
  6. Q6Compare to_char(now(), 'YYYY-MM-DD') vs ::date - both for date display.
  7. Q7Explain AGE(end, start) - what does it return vs (end - start)?
  8. Q8Compare INTERVAL '1 month' vs INTERVAL '30 days' - when do they differ?
  9. Q9Why does CURRENT_TIMESTAMP return the txn-start time, not now()?
  10. Q10Compare now() vs statement_timestamp() vs clock_timestamp().
  11. Q11Explain AT TIME ZONE - when does it convert vs assign?
  12. Q12What is DATE_TRUNC('week', ts) - and which day starts the week in Postgres?
  13. Q13How do you compute "business days between two dates" - give a SQL approach.
  14. Q14What is EXTRACT(EPOCH FROM ts) - and when is it useful?
  15. Q15Compare md5() vs sha256() (with pgcrypto) - when does each apply?
  16. Q16What is encode(bytea, 'hex' | 'base64') - and decode() reverse.
  17. Q17Explain url_encode (via plpgsql) - Postgres doesn't ship it; how do you build it?
  18. Q18What is to_tsvector - and why is it needed for full-text search?
  19. Q19Why are functional indexes (CREATE INDEX ON t(lower(email))) essential for sargable LOWER queries?
  20. Q20Compare CAST(x AS TYPE) vs x::TYPE - when does each fail differently.
  21. Q21What is FORMAT() - when is it preferred over string concatenation?
  22. Q22Explain how STRING_AGG with ORDER BY produces ordered concatenated lists.
  23. Q23Compare JSON_BUILD_OBJECT vs ROW_TO_JSON - when each.
  24. Q24Explain GENERATED ALWAYS AS (lower(email)) STORED - when use it.
  25. Q25What is the IMMUTABLE / STABLE / VOLATILE function classification - and why does it matter for indexes?

REGEX & STRING ADVANCED

  1. Q26Validate email with regex: SELECT email FROM customers WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'.
  2. Q27Extract domain from email using regexp_match.
  3. Q28Extract phone country code: regexp_match(phone, '^\+(\d{1,3})').
  4. Q29Split full_name into first/last/middle via regexp_split_to_array.
  5. Q30Find customers whose email contains digits: WHERE email ~ '\d'.
  6. Q31Strip non-digit characters from phone: regexp_replace(phone, '\D', '', 'g').
  7. Q32Mask middle of phone: regexp_replace(phone, '(.{3})(.+)(.{2})', '\1***\3').
  8. Q33Extract hashtags from a review_text: regexp_matches(review_text, '#(\w+)', 'g').
  9. Q34Find customers with consecutive duplicate letters: WHERE full_name ~ '([a-z])\1'.
  10. Q35Validate Indian PIN code: WHERE pin_code ~ '^\d{6}$'.
  11. Q36Find emails with uncommon TLDs: WHERE email !~ '\.(com|net|org|in)$'.
  12. Q37Replace whitespace with underscore: regexp_replace(name, '\s+', '_', 'g').
  13. Q38Extract numbers from product codes: regexp_replace(code, '[^0-9]', '', 'g').
  14. Q39Find duplicate words in subject: WHERE subject ~ '\\m(\\w+)\\M.*\\1'.
  15. Q40Use SIMILAR TO for SQL-standard pattern: WHERE name SIMILAR TO '[A-Z][a-z]+'.
  16. Q41Build a "slug" from a title: lower(regexp_replace(title, '\W+', '-', 'g')).
  17. Q42Extract third word: split_part(text, ' ', 3).
  18. Q43Find palindrome strings: WHERE col = reverse(col).
  19. Q44Count vowels in a string: char_length(col) - char_length(regexp_replace(col, '[aeiou]', '', 'gi')).
  20. Q45Truncate string at first whitespace: split_part(col, ' ', 1).
  21. Q46CASE-INSENSITIVE email match using LOWER + functional index.
  22. Q47URL-encode a parameter (DIY using regexp_replace).
  23. Q48Title-case a name: initcap(lower(full_name)).
  24. Q49Strip diacritics: unaccent(col) - requires unaccent extension.
  25. Q50Levenshtein distance for fuzzy match (requires fuzzystrmatch).

DATE/TIME ADVANCED

  1. Q51AGE between created_at and now: SELECT AGE(now(), created_at).
  2. Q52EXTRACT(YEAR/MONTH/DAY/HOUR FROM ts) - all four in one row.
  3. Q53Compute customer tenure in years: AGE(now(), created_at) -> EXTRACT(YEAR FROM ...).
  4. Q54Truncate timestamp to start of month / quarter / year.
  5. Q55Generate series of dates: SELECT generate_series('2025-01-01', '2025-12-31', '1 day')::date.
  6. Q56Find last day of the month: DATE_TRUNC('month', d) + INTERVAL '1 month' - INTERVAL '1 day'.
  7. Q57Compute end_of_week from any given date.
  8. Q58Compute "business days" between two dates (exclude Sat/Sun).
  9. Q59Subtract 90 days: now() - INTERVAL '90 days' vs now() - INTERVAL '3 months'.
  10. Q60Bucket orders by hour of day: EXTRACT(HOUR FROM order_date).
  11. Q61Convert UTC timestamp to IST: ts AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata'.
  12. Q62Find first Monday of each month using DATE_TRUNC + day-of-week math.
  13. Q63Compute "is_weekend": EXTRACT(DOW FROM d) IN (0, 6).
  14. Q64Compute days_to_due: (due_date - CURRENT_DATE).
  15. Q65ISO week number: EXTRACT(ISOWEEK FROM ts) or to_char(ts, 'IYYY-IW').
  16. Q66Compute "fiscal quarter" starting April: CASE-based on month.
  17. Q67Snap timestamp to 15-minute bucket.
  18. Q68Subtract intervals: AGE('2025-12-31', '2025-01-01') vs '2025-12-31' - '2025-01-01'.
  19. Q69Compute time-of-day buckets: morning/afternoon/evening/night via CASE on EXTRACT(HOUR ...).
  20. Q70Find orders placed in last 30 minutes.
  21. Q71Find shipments older than 30 days.
  22. Q72Convert epoch seconds to timestamp: to_timestamp(1700000000).
  23. Q73Find overlap of two date ranges: GREATEST(start1,start2), LEAST(end1,end2).
  24. Q74Compute "stay duration" in hours: EXTRACT(EPOCH FROM (end - start)) / 3600.
  25. Q75Compute moving window dates: a date and date - INTERVAL '7 days'.

REAL-WORLD DATA CLEANING

  1. Q76Normalize emails: LOWER(TRIM(email)).
  2. Q77Normalize phone: keep only digits, prepend +91 if missing.
  3. Q78Clean full_name: TRIM + INITCAP + remove extra spaces.
  4. Q79Find customers with leading/trailing whitespace in name (LENGTH(full_name) <> LENGTH(TRIM(full_name))).
  5. Q80Find double-spaced names: WHERE full_name ~ ' '.
  6. Q81Detect emails with no '@': WHERE email NOT LIKE '%@%'.
  7. Q82Detect non-ASCII characters in names: WHERE full_name ~ '[^\x00-\x7F]'.
  8. Q83Detect city names with unusual capitalization.
  9. Q84Strip non-alphanumeric from product codes.
  10. Q85Hash email for anonymization: md5(email).
  11. Q86Hash phone with sha256 (requires pgcrypto): encode(digest(phone, 'sha256'), 'hex').
  12. Q87Base64-encode a JSON payload: encode(payload_text::bytea, 'base64').
  13. Q88Find inconsistent date formats: e.g., column stored as TEXT vs DATE - find unparseable.
  14. Q89Detect orphan email domains: domains appearing < 3 times.
  15. Q90Detect impossible dates: birth_date > now() or birth_date < '1900-01-01'.
  16. Q91Compute customer age from date_of_birth (EXTRACT year from age()).
  17. Q92Auto-derive birth_year using LEFT(SSN-equivalent, 4) - not real, just a parsing exercise.
  18. Q93Auto-fix capitalization on city using INITCAP.
  19. Q94Combine first/last/middle into full_name with NULL-safe concatenation.
  20. Q95Build "display name" rule: prefer full_name, fall back to email prefix.
  21. Q96Build a search-index column: lower(full_name) || ' ' || lower(email) || ' ' || phone.
  22. Q97Build a GENERATED column for a normalized email.
  23. Q98Find rows where a JSON field is malformed (JSONB validation).
  24. Q99Identify columns where TRIM changes the value (whitespace bugs).
  25. Q100Build a "data quality report" - single row showing counts: bad_emails, missing_phones, dupe_names.

Production scenarios, optimisation

ADVANCED REGEX

  1. Q1Validate e-mail shape with an anchored POSIX pattern.
  2. Q2Capture the e-mail domain with regexp_match.
  3. Q3Extract a leading +country-code from a phone (capture group).
  4. Q4Split a full name into a token array on whitespace.
  5. Q5Extract ALL hashtags from a review (global flag).
  6. Q6Find names containing a doubled letter (backreference).
  7. Q7Strip every non-digit from a phone.
  8. Q8Mask a phone keeping first 3 + last 2 (replacement backrefs).
  9. Q9Emails whose local part contains a digit.
  10. Q10Emails NOT ending in .com/.net/.org/.in (negated match).
  11. Q11SIMILAR TO: a single capitalised word.
  12. Q12Slugify a ticket subject (non-word runs -> '-').
  13. Q13Subjects containing the same word twice (word-boundary + backref).
  14. Q14Count vowels in a name via length difference.
  15. Q15First whitespace token of the full name.
  16. Q16Palindrome check over a VALUES list (reverse).
  17. Q17Names containing a non-ASCII character.
  18. Q18Valid 6-digit pincode (addresses).
  19. Q19SUBSTRING(... FROM pattern) to capture the domain.
  20. Q20Capitalise the first letter via regexp_replace.
  21. Q21Case-insensitive keyword match in review text (~*).
  22. Q22Word count per review via regexp split + array_length.
  23. Q23Extract all numbers from free text.
  24. Q24Anchored alternation to validate an order status.
  25. Q25Find product names with leading/trailing whitespace.

STRING TRANSFORMATION & FORMATTING

  1. Q26Build a display name with concat_ws + NULLIF.
  2. Q27Build a log line with format() positional specifiers.
  3. Q28Zero-pad an order id to width 10 (lpad).
  4. Q29Strip a set of characters in one pass (translate).
  5. Q30Mask an email by position (overlay).
  6. Q31Normalise a city name with initcap+lower (addresses).
  7. Q32left/right slicing of an email.
  8. Q33Email domain via split_part (no regex).
  9. Q34string_to_array / array_to_string round-trip.
  10. Q35encode/decode hex round-trip.
  11. Q36encode/decode base64 round-trip.
  12. Q37URL-safe base64 via translate.
  13. Q38Built-in md5() email fingerprint (no pgcrypto).
  14. Q39Currency-style numeric formatting with to_char.
  15. Q40Text bar chart of orders per store (repeat).
  16. Q41ascii()/chr() round-trip.
  17. Q42starts_with() + length filter on emails.
  18. Q43btrim/ltrim/rtrim variants.
  19. Q44Extract the TLD via reverse + split.
  20. Q45Locate '@' with position().
  21. Q46Normalise a phone to +91XXXXXXXXXX.
  22. Q47Clean a name: collapse whitespace + initcap.
  23. Q48Human date label via to_char.
  24. Q49Compose a lowercase search blob from several columns.
  25. Q50Compare char_length vs octet_length (multibyte).

DATE/TIME MASTERY

  1. Q51AT TIME ZONE round-trip on a real timestamp (calls).
  2. Q52Convert a timestamptz literal to a wall-clock in another zone.
  3. Q53Last business day of the current month.
  4. Q54Quarter of a date shifted back 3 months.
  5. Q55ISO week vs US week.
  6. Q56Epoch (with fraction) to timestamp.
  7. Q57Month-over-month revenue via FILTER.
  8. Q58make_date / make_timestamp constructors.
  9. Q59justify_interval on 90 days.
  10. Q60Leap-year test via the calendar rule.
  11. Q61Format an interval as HHh MMm SSs.
  12. Q6215-minute bucket of a real timestamp (page_views).
  13. Q63Round a timestamp to the nearest 5 minutes.
  14. Q64DST-aware arithmetic on timestamptz.
  15. Q65Convert an order DATE to a zoned date.
  16. Q66Show two timestamptz literals are the same instant.
  17. Q67Business-day count in a month (weekends excluded).
  18. Q68Generate a full year of dates.
  19. Q69Next Monday from today (ISODOW).
  20. Q70Previous Friday from today.
  21. Q71Quarterly date series.
  22. Q72Account age in months from registration_date.
  23. Q73Business days a ticket stayed open.
  24. Q74Safe day-add across a month boundary.
  25. Q75AGE() vs subtraction (calendar-aware vs exact days).

FULL-TEXT SEARCH + MATCHING/HASHING TOOLKIT

  1. Q76Show stemming: 'running runs ran' -> 'run' (to_tsvector).
  2. Q77Reviews matching phone AND repair (inline tsvector @@ tsquery).
  3. Q78Rank full-text results with ts_rank.
  4. Q79Highlight matches with ts_headline.
  5. Q80Prefix full-text search ('phon:*').
  6. Q81Negation in a tsquery (phone & !repair).
  7. Q82Phrase search with phraseto_tsquery.
  8. Q83Google-style search with websearch_to_tsquery.
  9. Q84plainto_tsquery vs to_tsquery.
  10. Q85Weighted full-text over ticket subject (setweight).
  11. Q86Full-text + scalar filter (rating >= 4).
  12. Q87Stemming across english vs simple configs.
  13. Q88Generate a UUID token (built-in gen_random_uuid).
  14. Q89(practice) Persist a tsvector column + GIN index for fast search.
  15. Q90(practice) Create a custom text-search configuration (DDL + unaccent).
  16. Q91(practice) Trigram similarity() and % operator (pg_trgm).
  17. Q92(practice) Trigram distance ordering <-> with a GiST index.
  18. Q93(practice) Edit distance with levenshtein (fuzzystrmatch).
  19. Q94(practice) Phonetic matching: soundex/metaphone/dmetaphone.
  20. Q95(practice) SHA-256 / HMAC with pgcrypto.
  21. Q96(practice) bcrypt password hashing with crypt + gen_salt.
  22. Q97(practice) Symmetric encryption with pgp_sym_encrypt.
  23. Q98Describe a fuzzy near-duplicate detection pipeline (conceptual).
  24. Q99Describe a search-bar FTS-then-trigram fallback pattern (conceptual).
  25. Q100Build a privacy-safe customer export using only built-ins (md5 + base64).