Contribute OA questions
OAHelper
CompaniesProblemsTopicsInterview Experiences
Explore
Week 02

Week 02

String & Date Functions

Day 102 hrs

Window Functions I: ROW_NUMBER, RANK & Top-N per GroupWindow Functions II: LAG/LEAD, Running Totals & GapsString & Date FunctionsHard Company Problems: Combining EverythingSQL Interview Theory: The Questions They Actually Ask

Work with text and dates

Monthly revenue, booking lead times and email domains all need a small transformation before you can group or compare the data. The tricky part is choosing the right meaning: calendar days or elapsed hours, passengers or tickets, a month number or a particular month and year.

We will work through those choices using date ranges, string extraction and grouped text. The examples use SQLite, so check the function names when you move to another database.

Our running table for the day is an IRCTC style booking register. One row per passenger, and one PNR can hold more than one passenger.

bookings:

pnrpassengeremailjourney_datebooking_datefare
8412330071Rahul Vermarahul.verma@gmail.com2024-03-142024-02-28 21:40:001455
8412330071Anita Vermarahul.verma@gmail.com2024-03-142024-02-28 21:40:001455
8412330072Sneha Raosneha.rao@yahoo.co.in2024-03-022024-03-01 08:05:00640
8412330073Imran Shaikhimran.shaikh@gmail.com2024-04-062024-03-19 11:20:002310
8412330074Divya Nairdivya.nair@outlook.com2024-04-212024-04-20 23:10:00890
8412330075Karthik Iyerkarthik.iyer@gmail.com2024-05-012024-03-30 07:45:001980
8412330076Meera Joshimeera.joshi@rediffmail.com2024-05-122024-05-02 18:30:001120
8412330076Anil Joshimeera.joshi@rediffmail.com2024-05-122024-05-02 18:30:001120

8 rows, 6 tickets. journey_date is a plain date, booking_date carries a time as well. That one difference decides half the answers below.

What is String & Date Work in SQL?

SQLite has no dedicated date storage type. Dates can be stored as ISO text or as numeric timestamps. Our table uses text: journey_date has a date, while booking_date includes the time. typeof(journey_date) returns text for these rows.

columnvaluetypeof(...)
journey_date2024-03-14text
booking_date2024-02-28 21:40:00text
fare1455integer

Only fare is a real number, both date columns are strings.

For these text columns, keep four points in mind.

One, comparing two dates is a character by character string comparison, left to right. journey_date >= '2024-04-01' is not date logic, it is dictionary order.

Two, that comparison gives the right answer only because of the format. In YYYY-MM-DD the most significant part comes first and every part is zero padded, so dictionary order and time order are the same order. That is the entire reason date filters work in SQLite.

Three, change the format and the logic breaks. In DD-MM-YYYY, '14-03-2024' < '02-05-2024' is false, because '1' sorts after '0'. If an OA hands you another format, your first job is converting it to ISO.

Four, extract the part you need with a date or string function. Keep the full value when filtering a date range.

Two families of helpers, and you need both today.

String functions. LENGTH(s) counts characters, UPPER(s) and LOWER(s) change case. SUBSTR(s, start, len) cuts a piece out, counting from 1 and not 0, and a negative start counts from the right. INSTR(s, needle) says where a marker sits, or 0 when absent. REPLACE(s, a, b) swaps text, TRIM(s) removes surrounding spaces, a || b glues two values together.

expressionresult
LENGTH('2024-03-14')10
SUBSTR('Rahul Verma', 1, 5)Rahul
SUBSTR('Rahul Verma', -5)Verma
INSTR('rahul.verma@gmail.com', '@')12
REPLACE('8412330071', '84', 'XX')XX12330071

INSTR gives a position, SUBSTR does the cutting. They are almost always used as a pair.

GROUP_CONCAT(x, sep) is the odd one out. It is an aggregate, meaning it takes many rows and returns one value, and that value is all the inputs joined into one string.

Date functions. DATE(x) chops a timestamp down to YYYY-MM-DD, throwing the time away. strftime(fmt, x) is the workhorse: it pulls one named piece out of a date. %Y year, %m month, %d day, %w weekday from 0 for Sunday to 6 for Saturday, %s epoch seconds.

julianday(x) converts a date into a plain day number counted from a fixed point in history, so the gap between two dates becomes ordinary subtraction. date(x, '-1 month') does real calendar arithmetic that respects month lengths and year boundaries.

journey_datestrftime('%Y-%m', d)strftime('%m', d)strftime('%w', d)
2024-03-02'2024-03''03''6' (Saturday)
2024-03-14'2024-03''03''4' (Thursday)
2024-04-21'2024-04''04''0' (Sunday)

Every result is in quotes, and that is deliberate.

IMP

Important: everything strftime returns is text, zero padded. typeof(strftime('%m', journey_date)) is text. So strftime('%m', journey_date) = 3 is false for every row above, and the query returns zero rows with no error. Write = '03', or wrap it as CAST(strftime('%m', journey_date) AS INTEGER) = 3.

The Pattern

  1. Decide what you are pulling out of the raw value: a month key, a day gap, a substring, a joined list.
  2. Compute it once, in the SELECT with an alias, or in a CTE if you need it twice. A CTE is just a named temporary result written as WITH name AS (...).
  3. Group on the derived value when needed. For a date range, filtering the original column is often clearer and easier to index.
  4. Format at the very end only, with PRINTF or ||.
  5. Re-read the required output: column names, decimals, separator, row order.

Now build the standard monthly revenue question up from the smallest query. The ask: revenue by journey month, only journeys from 10 March onwards, keeping months that earned at least 3200.

Step 1. Just see the extracted value. Do not group yet. Print the month beside the raw date and check it looks sane.

SQL
SELECT passenger, journey_date, strftime('%Y-%m', journey_date) AS month
FROM bookings
ORDER BY journey_date;
passengerjourney_datemonth
Sneha Rao2024-03-022024-03
Rahul Verma2024-03-142024-03
Anita Verma2024-03-142024-03
Imran Shaikh2024-04-062024-04
Divya Nair2024-04-212024-04
Karthik Iyer2024-05-012024-05
Meera Joshi2024-05-122024-05
Anil Joshi2024-05-122024-05

Still 8 rows. Nothing is combined yet, we only added a derived column.

Step 2. Collapse the rows. GROUP BY folds all rows sharing a value into one output row so you can total them. It exists because "revenue per month" needs one line per month, not one per booking.

SQL
SELECT strftime('%Y-%m', journey_date) AS month,
       COUNT(*) AS seats,
       SUM(fare) AS revenue
FROM bookings
GROUP BY month
ORDER BY month;
monthseatsrevenue
2024-0333550
2024-0423200
2024-0534220

8 rows became 3, and SUM(fare) totalled the fares in each bucket.

Step 3. Add the two filters. WHERE throws away individual rows before grouping. HAVING throws away whole groups after the totals are computed. They are not interchangeable, and this question needs one of each.

SQL
SELECT strftime('%Y-%m', journey_date) AS month, SUM(fare) AS revenue
FROM bookings
WHERE journey_date >= '2024-03-10'
GROUP BY month
HAVING SUM(fare) >= 3200
ORDER BY month;

Dry Run:

StepWhat it does hereRows after this step
FROM bookingsreads the register8
WHERE journey_date >= '2024-03-10'text compare on ISO dates, drops Sneha Rao who travelled on 02 March7
GROUP BY monthbuckets on '2024-03', '2024-04', '2024-05'3
HAVING SUM(fare) >= 3200March is now only 2910, so that group goes2
SELECTprints the month key and the sum2
ORDER BY monthsorts the text, and for %Y-%m text order is time order2

Output:

monthrevenue
2024-043200
2024-054220

March is gone because HAVING cut it, not WHERE. Sneha's 640 was already removed, which is why March fell from 3550 to 2910 and missed the bar.

Read the query line by line.

  1. SELECT strftime('%Y-%m', journey_date) AS month takes each date string, keeps only the year and month part, and gives that derived value the name month.
  2. SUM(fare) says: for every bucket you make, add up the fare column and call it revenue.
  3. FROM bookings is where the rows come from.
  4. WHERE journey_date >= '2024-03-10' keeps only rows whose date string sorts at or after that text, one row at a time, before anything is combined.
  5. GROUP BY month folds all rows sharing a month key into one row each.
  6. HAVING SUM(fare) >= 3200 now looks at the folded rows and keeps only the ones whose total cleared 3200.
  7. ORDER BY month fixes the row order for the judge. Without it, SQLite may hand back the rows in any order.
  1. 1raw text column
  2. 2extract with strftime or SUBSTR
  3. 3alias it
  4. 4group or filter on the alias
  5. 5format for output
Check what you learned

Which query keeps only the March journeys from the bookings table?

Query Templates

SQL

SQL
-- 1. Bucket rows into calendar months, then aggregate.
SELECT strftime('%Y-%m', journey_date) AS month,
       COUNT(*) AS seats,
       SUM(fare) AS revenue
FROM bookings
GROUP BY month
ORDER BY month;

-- 2. Whole days between a timestamp and a date.
SELECT pnr, passenger,
       CAST(julianday(journey_date) - julianday(DATE(booking_date)) AS INTEGER) AS advance_days
FROM bookings
ORDER BY advance_days DESC, passenger ASC;

-- 3. Cut a string at a character you first have to locate.
SELECT SUBSTR(email, INSTR(email, '@') + 1) AS domain,
       COUNT(DISTINCT pnr) AS pnr_count
FROM bookings
GROUP BY domain
ORDER BY pnr_count DESC, domain ASC;

-- 4. Collapse many rows into one ordered, separated string.
SELECT pnr, GROUP_CONCAT(passenger, ', ') AS passengers, COUNT(*) AS seats
FROM (SELECT pnr, passenger FROM bookings ORDER BY pnr, passenger)
GROUP BY pnr
ORDER BY pnr;

Template 2, days in advance each seat was booked.

pnrpassengeradvance_days
8412330075Karthik Iyer32
8412330073Imran Shaikh18
8412330071Anita Verma15
8412330071Rahul Verma15
8412330076Anil Joshi10
8412330076Meera Joshi10
8412330074Divya Nair1
8412330072Sneha Rao1

Karthik booked more than a month early, Sneha and Divya booked one day before travel.

Now see what the same query prints without the DATE() wrapper. julianday returns a fractional day number, so the clock time leaks into the subtraction and CAST(... AS INTEGER) chops the fraction off towards zero.

passengerraw subtraction without DATE()wrong outputright output
Divya Nair0.034701
Sneha Rao0.663101
Rahul Verma14.09721415
Karthik Iyer31.67703132

Divya booked at 23:10 on the previous date, only 50 minutes before midnight. That is a calendar-day difference of 1 but less than one elapsed day. Choose the calculation that matches the question.

Template 3, bookings grouped by email domain.

domainpnr_count
gmail.com3
outlook.com1
rediffmail.com1
yahoo.co.in1

INSTR puts Rahul's @ at position 12, so SUBSTR from 13 onwards is the domain.

Two ways this one goes wrong:

what you typedwhat the judge printed
SUBSTR(email, INSTR(email,'@')), forgetting the + 1@gmail.com|3, @outlook.com|1, and so on, with the @ glued to the front of every group
COUNT(*) instead of COUNT(DISTINCT pnr)gmail.com|4 and rediffmail.com|2, because those PNRs carry two passengers each

The first mistake changes the text, the second changes the number. Both fail the diff on line one.

Template 4, all passengers of a PNR in one line.

pnrpassengersseats
8412330071Anita Verma, Rahul Verma2
8412330072Sneha Rao1
8412330073Imran Shaikh1
8412330074Divya Nair1
8412330075Karthik Iyer1
8412330076Anil Joshi, Meera Joshi2

The names inside each cell are alphabetical and there is a space after the comma. Both had to be arranged deliberately.

Tip

Tip: Newer SQLite versions support ordering inside GROUP_CONCAT. The example here also works on older versions. Order the rows in an inner subquery, which is just a query nested inside another one, and aggregate in the outer query, as template 4 does. Write plain GROUP_CONCAT(passenger) instead and the first row comes back as Rahul Verma,Anita Verma, in scan order, with no space after the comma.

What the judge sees

Check the required columns, output order and formatting against the sample. The practice judge compares the SQLite result:

So row order, decimal places and NULL formatting are part of the answer, not cosmetics. For the monthly report it compares this text:

Text
month|pnrs|avg_fare
2024-03|2|1183.33
2024-04|2|1600.00
2024-05|2|1406.67

That 1600.00 is why the query uses PRINTF('%.2f', AVG(fare)) and not ROUND(AVG(fare), 2). ROUND returns a number, and a number carries no trailing zeros, so it prints 1600.0. The diff then fails on one missing character while your logic was perfectly correct.

Variations

Variation 1 - Filter by a date range

Because ISO text sorts in the same order as time, a range needs no date function at all.

SQL
SELECT passenger, journey_date FROM bookings
WHERE journey_date >= '2024-04-01' AND journey_date < '2024-05-01'
ORDER BY journey_date;
passengerjourney_date
Imran Shaikh2024-04-06
Divya Nair2024-04-21

Two April journeys, found by string comparison alone.

A range is often preferable to strftime('%Y-%m', journey_date) = '2024-04', for a reason worth remembering. Wrapping a column in a function generally prevents a range lookup on its ordinary index. An expression index may help; check the query plan.

Keep the upper bound half open, < '2024-05-01' rather than <= '2024-04-30'. A row stamped 2024-04-30 18:00 sorts after '2024-04-30', so the <= version silently drops it.

Variation 2 - Weekday and weekend

strftime('%w', d) gives the weekday as text, '0' for Sunday through '6' for Saturday, so a weekend filter is IN ('0','6') with the values quoted.

CASE is a plain if then else inside a query. Output columns often need a label instead of a code, and here it turns '0' into the word Sunday.

SQL
SELECT pnr, passenger, journey_date,
       CASE strftime('%w', journey_date) WHEN '0' THEN 'Sunday' ELSE 'Saturday' END AS day_name
FROM bookings
WHERE strftime('%w', journey_date) IN ('0','6')
ORDER BY journey_date, passenger;
pnrpassengerjourney_dateday_name
8412330072Sneha Rao2024-03-02Saturday
8412330073Imran Shaikh2024-04-06Saturday
8412330074Divya Nair2024-04-21Sunday
8412330076Anil Joshi2024-05-12Sunday
8412330076Meera Joshi2024-05-12Sunday

Five seats but only four PNRs, since the Joshi ticket carries two passengers. "Bookings" and "passengers" are different counts here, so re-read the ask before you print.

Variation 3 - Previous calendar month

To compare a month against the one before it, never subtract 1 from the month number. January would become month 0, which is not a month. Build a real date and let SQLite roll it back.

SQL
SELECT strftime('%Y-%m', date('2024-01' || '-01', '-1 month'));   -- returns 2023-12

|| glues -01 on to make a valid date, then the modifier walks it back across the year boundary.

Variation 4 - Within N days, and same day travel

"Booked within a day of travel" is the tatkal case, the same subtraction as template 2 with a bound instead of an output column.

SQL
SELECT pnr, passenger FROM bookings
WHERE julianday(journey_date) - julianday(DATE(booking_date)) <= 1
ORDER BY pnr;
pnrpassenger
8412330072Sneha Rao
8412330074Divya Nair

Only the two last minute bookings survive.

"Consecutive days" is the same expression with = 1, usually compared against LAG(journey_date) OVER (PARTITION BY email ORDER BY journey_date) from yesterday's topic, where LAG hands you the previous row's value inside each passenger's group.

Variation 5 - LIKE vs GLOB

LIKE matches a pattern with % for any run of characters and _ for exactly one, and in SQLite it ignores case for plain ASCII letters. GLOB uses shell style wildcards * and ? and is case sensitive.

querymatches on our table
passenger LIKE 'a%'2 rows, Anita and Anil
passenger GLOB 'a*'0 rows
passenger GLOB 'A*'2 rows

Same intent, three different results, purely because of case handling.

So for "names starting with a capital A", use GLOB. For a plain "ends with gmail.com", email LIKE '%@gmail.com' is fine.

Equivalents in other dialects

These operations have different function names in MySQL and PostgreSQL.

SQLiteMySQLPostgres
strftime('%Y-%m', d)DATE_FORMAT(d, '%Y-%m')TO_CHAR(d, 'YYYY-MM')
CAST(strftime('%m', d) AS INT)MONTH(d)EXTRACT(MONTH FROM d)
julianday(date(b)) - julianday(date(a)) (calendar days)DATEDIFF(b, a)b::date - a::date
date(d, '-1 month')DATE_SUB(d, INTERVAL 1 MONTH)d - INTERVAL '1 month'
a || bCONCAT(a, b)a || b or CONCAT
GROUP_CONCAT(x, ', ')GROUP_CONCAT(x ORDER BY y SEPARATOR ', ')STRING_AGG(x, ', ' ORDER BY y)
INSTR(s, t)LOCATE(t, s)POSITION(t IN s)

The ideas are identical, only the spelling changes.

  1. 1locate the marker with INSTR
  2. 2cut with SUBSTR
  3. 3group on the cut value
  4. 4COUNT DISTINCT the ticket
  5. 5order for the judge
Check what you learned

Divya booked on 2024-04-20 23:10:00 for a journey on 2024-04-21. Which expression gives her advance_days as 1?

Common Mistakes

  • Comparing a text month with an integer. Use strftime('%m', d) = '03', or cast the result before comparing it with 3.
  • Combining the same month across years. Group by %Y-%m when March 2023 and March 2024 should be separate.
  • Mixing calendar days and elapsed time. Use DATE() before subtraction for a calendar-day difference. Keep the time when the question asks for elapsed hours or minutes.
  • Unordered joined text. Specify the passenger order and the separator before relying on a GROUP_CONCAT result.
  • Keeping the @ in a domain. INSTR returns the marker's position, so start SUBSTR one character later.
  • Counting seats instead of tickets. One PNR can have several passengers. Use COUNT(DISTINCT pnr) for tickets.
  • Rounding without formatting. ROUND changes the numeric precision. PRINTF('%.2f', x) gives text with exactly two decimal places.
  • Using a moving reference date. If the statement supplies an as-of date, use it. Use the current date only when that is what the question asks for.

Interview Notes

Why is it a problem that strftime returns text? Because everything downstream then behaves like string handling. Grouping and ordering happen in dictionary order, and a comparison against an integer is silently false instead of raising an error, so a broken filter just looks like an empty table. Follow up: then how do you compare it to a number safely?

How do you get the days between two dates in SQLite, MySQL and Postgres? In SQLite julianday(b) - julianday(a), in MySQL DATEDIFF(b, a), and in Postgres you subtract two date values directly, b::date - a::date. In all three I would strip the time part first if either column is a timestamp. Follow up: what happens if one of them is NULL?

How do you extract a domain from an email? SUBSTR(email, INSTR(email, '@') + 1). INSTR locates the @, SUBSTR takes everything after it, and the + 1 is there because INSTR points at the marker itself. The login part is the mirror image, SUBSTR(email, 1, INSTR(email, '@') - 1).

Is a range filter or a strftime filter better? For a particular date interval, start with a range on the original column. It expresses the boundaries clearly and can use an ordinary index. A function-based filter may need an expression index.

How do you find users who travelled on consecutive days? LAG on the date partitioned by the user, then keep rows where julianday(d) - julianday(prev) = 1. A self join on the same condition works too, and the clearer choice depends on the rest of the query. Follow up: now make it three consecutive days.

How do you concatenate strings in MySQL? With CONCAT(a, b). There || means logical OR by default, and only concatenates if PIPES_AS_CONCAT is set in the SQL mode. This comes up often, because most people learn || first.

What is the Postgres equivalent of GROUP_CONCAT? STRING_AGG(x, ', ' ORDER BY y). It takes its own ORDER BY inside the function call, which controls the order of the joined values.

Can you bucket rows into months without strftime? Yes. On ISO text, SUBSTR(journey_date, 1, 7) gives '2024-03' directly. It is a plain string cut, faster than parsing the date, and it produces the same key.

Why avoid DATE('now') in an answer? The result changes every day, so no grader can hold a fixed expected output against it. If the question implies a current date, take it as a parameter or use the literal the statement gives.

Quick Test

Check what you learned

SELECT SUBSTR(email, INSTR(email, '@') + 1) FROM bookings WHERE passenger = 'Sneha Rao';

1/5

Day 10

Finished this topic?

Mark it done and your roadmap moves to the next day.

Up nextHard Company Problems: Combining Everything
PreviousWindow Functions II: LAG/LEAD, Running Totals & Gaps

On this page

Summarise with AI

  • ChatGPT
  • Claude
  • Perplexity
  • Grok
  • Google AI Mode
Your next OA is closer than you think.Real questions from this season's OAs
ProductCompany OAsAll ProblemsOA CalendarMock OAsDSA RoadmapSQL RoadmapAgent Engineering
ResourcesInterview experiencesTopicsOA GroupsContributeCompany InsightsOA StorePremiumFor Colleges
OA Helper, powered by NxtWave
TermsPrivacyRefundsTrust & SafetyContact© 2026 OA Helper

Disclaimer: OAHelper is an independent educational platform. We (oahelper.in) do not own the images or questions shown. Content is uploaded by users.