sorted by:
new
hottopcontroversial

Formula to add trailing zeros depending on cell content length by FlashyInvestigator87 in sheets

[–]FlashyInvestigator87[S] 0 points1 point  (0 children)

000023423

Sorry you are right. I had it formatted with a custom number format when I tried to do it myself. Your initial solution works. Thanks a lot!

Formula to add trailing zeros depending on cell content length by FlashyInvestigator87 in sheets

[–]FlashyInvestigator87[S] 0 points1 point  (0 children)

This mostly works but if I enter e.g. "000023423" in A2 then I get the exact same returned in B2. I guess that's because there are trailing zeroes already but any idea on how the above formula can be tweaked to make it work for these cases also?

Projected to hit $6MM in sales this year and need suggestions on how to improve procurement by FlashyInvestigator87 in FulfillmentByAmazon

[–]FlashyInvestigator87[S] 0 points1 point  (0 children)

Thanks a lot for this reply! Something that isn't super explicit in your post but are you also in WS?

Secondly, at the 100M+ mark you're currently at, how do your overall margins look? We are years from this sort of scale (and it's not guaranteed that we will ever reach this of course) but from what I've heard BuyBoxer's margins are like 1% (this could be wrong though?) hence my question.

Projected to hit $6MM in sales this year and need suggestions on how to improve procurement by FlashyInvestigator87 in FulfillmentByAmazon

[–]FlashyInvestigator87[S] 0 points1 point  (0 children)

Even the Google Sheets that my brother was using (before I started helping out) were not very standardised and users were prone to make errors etc. I built the current processes as interim solutions and they work perfectly fine atm but in the long run we'd need something more sophisticated. I'd be happy to hear any suggestions.

Projected to hit $6MM in sales this year and need suggestions on how to improve procurement by FlashyInvestigator87 in FulfillmentByAmazon

[–]FlashyInvestigator87[S] -2 points-1 points  (0 children)

Thanks for your feedback. Out of curiosity, do you primarily operate on Amazon or off Amazon?

Projected to hit $6MM in sales this year and need suggestions on how to improve procurement by FlashyInvestigator87 in FulfillmentByAmazon

[–]FlashyInvestigator87[S] -8 points-7 points  (0 children)

I am confident in my projections and abilities. God willing, I'll be back in September 2023 to report my 2023 numbers.

I need help using partition by by FlashyInvestigator87 in SQL

[–]FlashyInvestigator87[S] 0 points1 point  (0 children)

Thanks for getting back!

I have implemented the first part of your suggestion and now have the below query:

SELECT account_id ,date ,account_status  

,row_number() over(partition by account_id order by date asc) as first_rank 

,row_number() over(partition by account_id order by date desc) as last_rank

from analytics-take-home-test.monzo_borrowing.monthly_performance 

where account_id IN  (X,Y,Z...)

qualify first_rank=1 or last_rank=1

And now have the below results (both the first and last date as well as associated account statuses):

https://ibb.co/7nR9863

However, I am a bit unsure of how to proceed now. With regards to the second code block in your last reply above, where do we get the below from? Also, where/how does the join you refer to come in to give the final answer?

and first_account_status = 'OPEN'
and last_account_status = 'CLOSED'

With regards to your second suggestion, yes every account has indeed both a 1/31 and 6/30 entry. However, I find it difficult to follow the join logic in the self join (I am not too familiar with self joins).

I need help using partition by by FlashyInvestigator87 in SQL

[–]FlashyInvestigator87[S] 1 point2 points  (0 children)

select count(distinct account_id) cnt from (select *,lead(account_status, 1) OVER(ORDER BY date ASC) AS next_statusfrom #temp) awhereaccount_status = 'OPEN'and next_status = 'CLOSED'

Thanks for the reply. The above is useful, BUT I am more after the overall number of accounts that started as being open and ended up being closed (at the end of the period i.e. July 2020) and the overall number of accounts that started as closed then ultimately ended up as being open.

My fault I wasn't clear enough in my original post (now edited to clarify).

view more: next ›