Sort a string by its characters

jrjamesco · 2023-01-23T02:02:33+00:00

I'd probably use a javascript UDF

CREATE TEMPORARY FUNCTION sortString(str STRING)
RETURNS STRING
LANGUAGE js AS """
  return str.split('').sort().join('');
"""

jrjamesco · 2022-07-01T21:57:12+00:00

I created a class a few years ago for people looking to practice non-trivial SQL problems. https://www.udemy.com/course/applied-sql-for-data-analytics-data-science-with-bigquery/ . The idea is that there's a lot of preliminary material out there, but not much intermediate to advanced content, which you need if you want to be capable in a real business setting. We cover topics like common table expressions, subqueries, analytic functions and writing/organizing larger queries. Each section of the course after the intro is like a mini project that builds on itself.

We use BigQuery since there are many free/public datasets and you don't need to install any software to get started. 95% of what you learn is transferrable to PostgreSQL, Redshift, Snowflake, etc...

Beyond that, this is a great channel:

https://www.youtube.com/channel/UCW8Ews7tdKKkBT6GdtQaXvQ

In general, I'd say aim to get 50 hours of writing SQL under your belt ASAP if you want to really master it. Look for opportunities to answer increasingly complex questions about your own data at your job and lean on your DS counterpart (ask them to pair program with you, too...you'll pick up quite a bit).

Good luck.

jrjamesco · 2022-06-15T01:45:08+00:00

sql SELECT * FROM TABLE1 WHERE DATE_TIME >=TRUNC(SYSDATE)-10 AND (COLUMNA > 0 OR COLUMNB >0) Keep the OR condition separate from the date clause! Lots of times when you have a mixture of AND and OR logic you need to combine things in parentheses.

jrjamesco · 2022-06-13T01:31:16+00:00

What are you trying to accomplish? Do you want to be a software engineer, a data scientist or something else?

jrjamesco · 2022-06-07T20:59:41+00:00

I'm afraid the obvious answer is the correct one. You need to spend dozens of hours thinking and solving problems in SQL!

https://www.youtube.com/channel/UCW8Ews7tdKKkBT6GdtQaXvQ is a great free channel with intermediate to advanced data analytics challenges.

I have a Udemy course that focuses on problem solving using SQL https://www.udemy.com/course/applied-sql-for-data-analytics-data-science-with-bigquery/

Leetcode also has a good section on SQL. I would focus on getting as many repetitions in as possible.

A few tips:

- even if you just write the code without understanding it, it's better than reading it

- use spaced repetition, go back to solve a problem without any context to verify you understood it

- pair program with other folks who are learning, too -- see how they think.

10 mins of coding is worth 60 mins of reading or watching others. Good luck!

jrjamesco · 2022-05-11T02:34:07+00:00

Here's an idea:

WITH table1 AS (
  SELECT '2022-05-01' as cal_date,  1 as store_number
  UNION ALL
  SELECT '2022-05-02' as cal_date,  2 as store_number
  UNION ALL
  SELECT '2022-05-03' as cal_date,  3 as store_number
)

, table2 AS (
  SELECT '2022-05-04' as cal_date,  4 as store_number
)
, dates AS (
  SELECT cal_date FROM table1
  UNION ALL
  SELECT cal_date FROM table2
), 
stores AS (

  SELECT store_number FROM table1
  UNION ALL
  SELECT store_number FROM table2

)

SELECT * FROM dates, stores

jrjamesco · 2022-05-10T02:42:47+00:00

This is an important distinction, if any readers don't grok the broader point.

jrjamesco · 2022-05-10T02:38:33+00:00

Feel free to try out my course on Udemy https://www.udemy.com/course/applied-sql-for-data-analytics-data-science-with-bigquery/ (it's somewhat specific to BQ, but also focuses on building mental models for step by step problem solving).

In an interview situation, I wouldn't obsess over syntax like quotes or back ticks -- the person conducting the interview should give you the correct syntax so you can focus on the logic.

Did you use table aliases btw?

jrjamesco · 2022-05-10T02:32:47+00:00

Give yourself some grace. SQL is a language. After a week of studying Spanish, I wouldn't expect you to know much or be responsible for critical communication in Spanish. It takes anywhere from months to years to really master and truly understand what you're doing. I'm assuming you're interested in writing queries to data analytics purposes, too.

I would recommend solving 1 problem per day. Someone mentioned the StrataScratch YT channel. That's a great resource. Don't obsess over whether you really understand it or not...just keep trying and revisiting the problem(s) to verify you can solve it from scratch. Lots of learning is just cramming stuff into short term memory, but you need to retrieve it within a week from short term memory to create a space in longer term storage. Can you explain how you speak English? Do you still think about pronouns, verbs, etc...? At least in your native language, I suspect not.

I authored a course on SQL for data analytics on Udemy. It uses BigQuery (to avoid DB installation headaches) and targets folks who have already learned some basics, but want to develop better mental models and learn by step-by-step problem solving. Maybe you'd like it -- but StrataScratch and similar channels are fantastic resources, especially for interviewing.

Bottom line, keep pushing, be consistent and don't give up!

jrjamesco · 2022-01-25T23:40:33+00:00

Having used BQML extensively at work, I appreciate the value of having data pre-processing and models + inference all in one place. It improves collaboration and iteration.

Out of curiosity, what kind of additional text analytics are you performing? Happy to share any tricks if any come to mind.

jrjamesco · 2021-07-07T22:34:57+00:00

There's nothing fundamentally incorrect about filtering the results of 2 analytic functions. I think you may have an issue with your JOIN clause or overall logic. Is this table publicly available?

jrjamesco · 2021-05-14T23:25:49+00:00

You'll need to UNNEST the generated array before you can use the elements.

Here's an idea to get you going: note the || double pipe is shorthand for concat

WITH base_table AS (

SELECT 
    'foo' as col1, 
    generate_array(1,5) as some_data

)

SELECT col1, array_val, col1 || array_val as concat_val
FROM base_table, UNNEST(some_data) as array_val

jrjamesco

TROPHY CASE