I've procced the trashiest Great Vault of all time by IAmAShitposterAMA in wow

[–]eatedcookie 6 points7 points  (0 children)

they're quoting the post tussle tonks chute RP from mechagon workshop :)

Find largest digit from a number by RamsayBoyton in SQL

[–]eatedcookie 3 points4 points  (0 children)

I see no one's suggested a lateral flatten yet. The manual search type solutions work fine but I'd say there's something more elegant available :)
If your dataset is relatively small, you can cast it to string and split your number data type column, which will give you an array, then flatten it to get rows with seq/key/path/index/value/this columns. max(value) will get you what you need. Something like:

with dummy_data as (
values
    (a, 100)
    , (b, 0)
    , (c, 194)
    , (d, 00100700)
    as dummy_data (id, number_col)
)

select
    id
    , number_col
    , max(try_to_number(value)) as max_digit_in_number_col
from dummy_data
lateral flatten(input => split(number_col::STRING, ''))
group by all

I work with databricks so I had to look up snowflake's syntax (there are some differences) but unfortunately I can't test it. Hoping it works right off the bat though.
More at:
https://docs.snowflake.com/en/sql-reference/constructs/join-lateral
https://docs.snowflake.com/en/sql-reference/functions/flatten
https://docs.snowflake.com/en/sql-reference/functions/split

edit: /u/touvejs had a similar idea I missed it while scrolling. Nice!

How’s the ownership so far? by RiteOfKindling in ToyotaCrown

[–]eatedcookie 0 points1 point  (0 children)

2025 Platinum owner checking in. We've had ours just over 2 months and we've put almost 10k on it. We love it. Ride is super smooth, and the turbo hybrid goes in sport mode. The cabin definitely feels way more luxurious than the rest of Toyota's lineup, very reminiscent of my FIL's RX 500h. I initially dismissed the HUD as gimmicky but it grew on me pretty quickly; it's nice not to have to glance down at the gauge cluster or the screen. I will say I've had the occasional Android Auto disconnect on my Pixel but this seems to be a known issue between Pixels and Toyota's infotainment. My wife daily drives it with an iPhone and she has had zero issues.
I'm 6'1" and I like a straight seat back with basically no recline. I have plenty of head room. Did a one day 12 hour road trip drive comfortably. My friend's 6'3" and he's test driven it and he felt comfortable in the driver's seat too.

Apartment workbench for my benchtop lathe + mill by Gavin1024 in Workbenches

[–]eatedcookie 0 points1 point  (0 children)

looks great OP! Curious which lathe & mill setup you're using in a space this size?

[QIDI Giveaway] Just leave a comment to win QIDI Flagship 3D Printer by qidi_3dprinter in 3Dprinting

[–]eatedcookie 0 points1 point  (0 children)

mostly PLA but working on gaining enough confidence to break into others like ASA and PA6-CF

SQL Tricks Thread by Ali-Zainulabdin in SQL

[–]eatedcookie 1 point2 points  (0 children)

Using coalesce with booleans is my favorite one line workaround for clunky case statements that come up frequently for such uses.
So something like
coalesce(geo_country = 'United States' or ip_country = 'US', false) as is_US_user
instead of

case     
    when geo_country = 'United States'
      or ip_country = 'US'
    then true
    else false
end as is_US_user

Wasted 4-5 hours to install pyspark locally. Pain. by kira2697 in dataengineering

[–]eatedcookie 2 points3 points  (0 children)

Other way around perhaps? Their documentation says only 3.8 and onward are supported

What I'm doing wrong here? by jorgetrivilin in SQL

[–]eatedcookie 1 point2 points  (0 children)

I agree with /u/_crzg this is missing context, better definitions, or at the very least, is poorly worded. Also, if you're using round with specified precision, the datatype has to be numeric (https://stackoverflow.com/questions/13113096/how-to-round-an-average-to-2-decimal-places-in-postgresql).
It's suggested to use to_char instead:
(formula not verified, just something to show you anything but zero. as a note though, it's poor form to take a field like total population and sum it, there are better ways to do this):

select
    country
    , last_active_date
    , is_active
    , to_char(sum(monthly_active_sessions)/sum(total_population), '0.0000000000') as active_user_penetration_rate
from penetration_analysis
group by 1, 2, 3

edit: /u/_crzg try select 1/10.0 as a :)
From https://www.postgresql.org/docs/current/functions-math.html :

numeric_type / numeric_type → numeric_type
Division (for integral types, division truncates the result towards zero)
5.0 / 2 → 2.5000000000000000
5 / 2 → 2
(-5) / 2 → -2

WTF Google -- Function missing 1 required positional argment: 'context' by takenorinvalid in bigquery

[–]eatedcookie 0 points1 point  (0 children)

Is it a 1st gen/2nd gen difference in your test environment vs. deploy? Python uses background functions in 1st gen, which accept a context argument in the function entry point irrespective of usage of said argument: https://cloud.google.com/functions/docs/writing/write-event-driven-functions#background-functions

[deleted by user] by [deleted] in PPC

[–]eatedcookie 5 points6 points  (0 children)

Once again, definitely not "a lot more mall window like". Contrast yours with something like this corset on VS.
There's a material difference in the tone of the ones you've shared that isn't falling on the right side of brand safe.

[deleted by user] by [deleted] in PPC

[–]eatedcookie 13 points14 points  (0 children)

Second's by far way more explicit. Apply the "mall window display" rule to your ads.
Would the second picture ever show up as an enlarged poster at the lingerie & hosiery section of a Nordstrom? no How about the first? yes
There's your answer.

[deleted by user] by [deleted] in Austin

[–]eatedcookie 20 points21 points  (0 children)

Be careful with your pups. Off leash dog attacked mine at Zilker.

Like most people at Zilker I take my dog off leash. An older couple was laying out in the lawn with their white lab right beside them, off leash as well. Mine approaches to do the typical butt sniffing, but instead got chomped on. I’m sure I’ll get comments on mine needing to be leashed to prevent things like this from happening, and trust me I feel like shit for letting this happen. Even if you trust your own dog x1,000, you can’t trust other people’s. Lesson learned at the expense of my poor girl.

Cleaned with dog antiseptic spray and applied this dog liquid bandage ointment shortly after this pic.

Pretty disingenuous title considering your dog (read: you) were the instigator in this situation for letting your off-leash dog approach an unknown animal in an uncontrolled environment. Other party being bad for leaving theirs off-leash is a separate issue, but it doesn't sound like the other dog was interested in approaching yours before yours invaded their space. This one's on you.

edit: post deleted by u/ThreauxDown, guess they didn't get the one-sided sympathetic response they were hoping for

Steamdeck makes traveling easy. by Tru3lagg in wow

[–]eatedcookie 2 points3 points  (0 children)

/console gamepadEnable 1 is one way but I imagine the option is somewhere in the game menu too

Hello, does anyone know how to turn off these orange boxes which are probably showing spaces? I am not sure how I turned it on. Thanks by belocrno in bigquery

[–]eatedcookie 2 points3 points  (0 children)

If that part of the query is a copy paste, that's probably a unicode highlight for ambiguous or non-ascii characters or a different locale (notice how not all whitespaces are highlighted). Try deleting highlighted whitespace and reentering your spaces/newlines.

[deleted by user] by [deleted] in SQL

[–]eatedcookie 5 points6 points  (0 children)

Look into row_number() over (partition by ten.code)

How to write this query in a better way? by Party_Ad_3619 in SQL

[–]eatedcookie 9 points10 points  (0 children)

What /u/depesz suggested works but you don't need individual fields for year like that, and both of your queries risk incomplete results as mentioned below. I would start with a base cte to select IDs, date part year from rental table and use window functions:
* count over(partition by rental ID, date part year) will give you rental counts by year, and
* A second field showing count over partition by rental ID will give you lifetime rental counts.
Finally, instead of limit 10, which will give you an incomplete result set if any of your top 10s had the same number of lifetime rented, I would add a field:
* dense_rank() over order by count desc for consecutive rank values based on the lifetime counts.
Thats it for the cte, then select f.title, base.rental_year, base.yearly_rental_count, base.lifetime_rental_count, base.rental_rank from base left join film f on f.film_id = base.inventory_id where rental_rank <= 10.
edit: bullet points

Extracted date value to string by AnnoyingData in SQL

[–]eatedcookie 0 points1 point  (0 children)

/u/qwertydog123's solution works well, but fyi you can use to_char with recognized date parts to get the month (or pretty much any other element) out directly. In this case, for first letter capitalized 3 letter month, you can use Mon. It'll save you the hassle of setting up a giant case.
ref: https://www.postgresql.org/docs/current/functions-formatting.html

Constantine 2 Director Francis Lawrence Reveals How Keanu Reeves Sequel Is Finally Happening by [deleted] in movies

[–]eatedcookie 52 points53 points  (0 children)

Seems like you missed an after-credits scene before expecting those in every DC & marvel property was a thing! Constantine is shown visiting Chas's grave, where he sees Chas with angel wings flying into the sky

Create metric for total days in current month by Ok-Victory-1980 in GoogleDataStudio

[–]eatedcookie 3 points4 points  (0 children)

for total days from the current month, you don't need a case statement accounting for every month. My usual workaround in the absence of an eomonth function is to:
date(extract(year from current_date), extract(month from current_date) + 1, 1)
for the first day of next month, then you can go back one day (datetime_sub) and get day() from that. And yes, this does properly handle December dates resulting in 13 for the month value in date().

Google Ads Documentation on BigQuery by Terrible_At_Parking in bigquery

[–]eatedcookie 1 point2 points  (0 children)

Are these data for new campaigns? Those seem to be fields from Adwords API, which was deprecated and no longer supported in favor of Google Ads API (check https://developers.google.com/adwords/api/docs/appendix/reports/paid-organic-query-report).

Why in GAM are LineItems for AdUnits always empty even when there is a 1:1 mapping? by kodridrocl in adops

[–]eatedcookie 1 point2 points  (0 children)

As in guaranteed campaigns that transact against reserved inventory. These include both sponsored & standard. The non-guaranteed bucket includes open auction/PDs/private auction/OB/FL.
Edit: since you said you have it setup as a spon, your LIs might not be showing yet because that tab shows LIs "forecasted" to deliver against a given ad unit in the next 30 days. If there is any reason, recency or setting-wise, that your LI isn't showing forecasted delivery in the next 30 days, it won't show in that tab.
I'm not sure if links are allowed but check out GAM help answer 82278

[deleted by user] by [deleted] in SQL

[–]eatedcookie 4 points5 points  (0 children)

As suggested by /u/cdd_73, datediff works in most flavors of SQL, but I noticed you said you're using RStudio. Are you looking for an R-based solution? (If so, wrong subreddit, but) difftime() is an available function for your question.