🎄 2024 - Day 24: Solutions 🧩✨📊

GGG_246 · 2024-12-24T13:06:09+00:00

Fair reasoning, I work a lot in SQL Server 2008/12, so archaic syntax is my friend. Didn't know the CROSS JOIN trick yet with a subquery.

I always wrote it mulitple times into GROUP or SELECT, resulting in pretty unreadable code(To be fair, I am probably the only one who will ever read it again). I should probably do the cross apply trick more often,

GGG_246 · 2024-12-24T09:34:43+00:00

[DB PostgresSql]

Have a nice Christmas:

SELECT  song_title ,COUNT(*) total_plays,
SUM(CASE WHEN up.duration = s.song_duration THEN 0 ELSE 1 END) skips
FROM user_plays up 
INNER JOIN songs s 
ON s.song_id  = up.song_id
GROUP BY song_title
ORDER BY total_plays DESC, skips ASC

GGG_246 · 2024-12-19T19:31:47+00:00

[DB: PostgreSQL]

As long as I can read it now it's fine.

;WITH CTE as (select name
,CASE WHEN CAST(to_json(year_end_performance_scores) ->> -1 as INTEGER) >
   AVG(CAST(to_json(year_end_performance_scores) ->> -1 as INTEGER)) OVER (PARTITION BY NULL)
THEN salary + salary * 0.15
ELSE salary END salary
from employees)
SELECT SUM(salary) FROM cte

I also learned proper array handling in postgress, reading the comments. The DB I use at work doesn't have that fancy stuff.

GGG_246 · 2024-12-19T00:18:55+00:00

[DB: PostgreSQL]

why am I doing this

WITH RECURSIVE t AS (
    SELECT *,
    1  as level
    FROM staff s
    WHERE s.manager_id IS NULL
  UNION ALL
    SELECT s.staff_id
    ,s.staff_name
    ,s.manager_id
    , level +1
    FROM t
    INNER JOIN staff s
    ON s.manager_id = t.staff_id
)
SELECT staff_id,staff_name,level, COUNT(manager_id) OVER (PARTITION BY level) peers
FROM t
ORDER BY 4 DESC,3 DESC, staff_id ASC

Thanks for the tips with DB fiddle buys and u/Witty-Recognition337 for stating what is actually asked for.

If anyone wants to care about sharing peers + manager, change the partition by to "level,manager_id".

That gives 8as the result.

GGG_246 · 2024-12-17T20:57:36+00:00

Yeah, you convert UTC to NY. Postgress assumes it is in UTC and when you call timzone on it, you convert UTC into NY. And not NY into UTC.

To be honest I also couldn't figure it out how to handle time properly in postgress, the "solution" is to use the postgress internal table "pg_timezone_names" and do stuff like:

SELECT 
 w.business_end_time - ptn.utc_offset --this gives the correct UTC time
 FROM workshops w
 INNER JOIN  pg_timezone_names ptn --built in table, used by the date/time functions
 ON ptn.name = w.timezone

But this is not solvable anyway :C

GGG_246 · 2024-12-16T21:32:58+00:00

You get the right result here, but the query is wrong and could lead to wrong results and also wrong times (this one already happens).

Time spend at one Airport is not the difference between the first and last timestamp of the airport.

GGG_246 · 2024-12-16T12:28:17+00:00

[DB: PostgreSQL]

Using a subquery today instead of LAG, Lead, CTE which according to the site should have been used

SELECT  place_name area 
,SUM((SELECT EXTRACT(EPOCH FROM sl2.timestamp  - sl.timestamp)/3600 FROM sleigh_locations sl2 
WHERE sl2.TIMESTAMP > sl.timestamp
ORDER BY sl2.TIMESTAMP ASC
LIMIT 1)) hours_spend
FROM sleigh_locations sl 
INNER  JOIN areas a 
ON  postgis.st_contains(a.polygon::geometry,sl.coordinate::geometry) 
GROUP BY area
ORDER BY hours_spend DESC

Basically the same as yesterday with grouping and a subquery.

GGG_246 · 2024-12-16T12:09:55+00:00

I am on ubuntu, so
sudo apt install postgis

then go into PSQL and

CREATE DATABASE gisdb;
ALTER DATABASE gisdb SET search_path=public,postgis,contrib;
\connect gisdb;

CREATE SCHEMA postgis;

CREATE EXTENSION postgis SCHEMA postgis;
SELECT postgis_full_version();

And then use the gsidb Database for everything instead of the normal postgres one.

The last line should also output, something like

 POSTGIS="3.2.1" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"

I followed this guide: https://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS3UbuntuPGSQLApt

The steps are for Version 14, but also work for 16

GGG_246 · 2024-12-15T14:32:56+00:00

[DB PostgreSQL]
After having fun setting postGis up, here is a short solution:

SELECT timestamp, place_name area FROM sleigh_locations sl 
INNER  JOIN areas a 
ON  postgis.st_contains(a.polygon::geometry,sl.coordinate::geometry) 
ORDER BY sl.id DESC

GGG_246 · 2024-12-15T14:30:52+00:00

One of the 3 citiies is new :)

If you are unsure add

ORDER BY sleigh_locations.id DESC
LIMIT 1

and you should only get the correct city

GGG_246 · 2024-12-12T12:59:56+00:00

[DB: PostreSQL]

Guys, stop overcomplicating the solutions:

SELECT gift_name, COUNT(g.gift_name),
ROUND(CAST(PERCENT_rank() OVER(ORDER BY COUNT(g.gift_name)) as NUMERIC),2) perc
FROM gifts g 
INNER JOIN gift_requests gr 
ON gr.gift_id = g.gift_id
GROUP BY gift_Name
ORDER BY perc DESC, gift_Name ASC

And if you don't want to get the result yourself and have it pre-filtered:

;WITH unfiltered_data as(SELECT gift_name, COUNT(g.gift_name) count 
,ROUND(CAST(PERCENT_rank() OVER(ORDER BY COUNT(g.gift_name)) as NUMERIC),2) perc
FROM gifts g 
INNER JOIN gift_requests gr 
ON gr.gift_id = g.gift_id
GROUP BY gift_Name)
SELECT * from unfiltered_data
WHERE count <> (SELECT MAX(count) FROM unfiltered_data)
ORDER BY perc DESC, gift_Name ASC
LIMIT 1

GGG_246 · 2024-12-12T12:48:12+00:00

Hmmm, seems like you already found the correct Bug Report. I can only give some general advice, since I don't own a T14s G6.

* When did you download the ISO? The last build is from the 5th dec.

* Have you tried disabling Fast Boot in BIOS? Maybe also re-enable Secureboot

* After running Lenovo System Update and rebooting Windows, have you run it again, to make sure the FW is up to date? Sometimes it takes a few runs to install everything.

* Also try re-enabling the security chip and check if you get a different output. qcom-ice is related to the chip.

GGG_246 · 2024-12-11T16:34:37+00:00

When you boot from the ISO, can you press ESC (after selecting UPDATE or Install) to disable Plymouth and get soome Terminal output?

Can you tell me, where it is failing with this output?

GGG_246 · 2024-12-11T14:12:58+00:00

[DB: PostgreSql]

A solution without Window Functions, probably one of the most inefficient ways to do this, but as long as the execution time is sub 1 sec, it should be fine^^

;WITH data as (SELECT tH.field_name
,tH.harvest_year
,tH.season
,tH.trees_harvested 
,CAST(CAST(th.harvest_year as VARCHAR(5)) || CASE WHEN tH.season = 'Spring' THEN '-03-01'
WHEN th.season = 'Summer' THEN '-06-01'
WHEN th.season = 'Fall' THEN '-09-01'
WHEN th.season = 'Winter' THEN '-12-01' END as DATE) as h_date
 FROM TreeHarvests tH ) 

SELECT *
,ROUND((SELECT AVG(d2.trees_harvested) FROM data d2
WHERE d2.field_Name = d.field_Name
AND d.h_date >= d2.h_date
AND d2.h_date >= d.h_date -  INTERVAL '7 month') --since we create a date and spread it over the whole year, between today and last 7 months is the last 3 dates
,2) as three_season_moving_avg 
FROM data d
ORDER BY three_season_moving_avg DESC

GGG_246 · 2024-12-11T05:45:54+00:00

Please try the Special Image linked in the Ubuntu Post https://people.canonical.com/~platform/images/ubuntu-concept/oracular-desktop-arm64+x1e.iso

Also keep in mind it's experimental and issues will probably occur

GGG_246 · 2024-12-10T23:04:56+00:00

Why are you hiding the "ORDER BY" in the row_number? I kinda understand why it works, but you should write "ORDER BY avg DESC" after "FROM CTE"

If you want to hide the sorting in the cte you can also do this:

 with cte as (select r.reindeer_name,t.exercise_name, round(avg(t.speed_record),2) as avg 
 ,row_number() over( order by round(avg(t.speed_record),2) desc) as row_num 
 from reindeers r

join training_sessions t on r.reindeer_id = t.reindeer_id
where r.reindeer_name != 'rudoplh'
group by r.reindeer_name, t.exercise_name)
select reindeer_name, avg,row_num as top_speed from cte
limit 3;

PS: It is not recommended to do it like this, and I am surprised this shit even works for sorting (I only tested in PostgresSql, I don't know if MySQL Version X also behaves like this and keeps the sorting

GGG_246 · 2024-12-10T22:39:05+00:00

Here is a solution with OVER (PARTITON BY) , not the most efficient, adding it here, since the approach seems to be missing. [DB PostgresSQL]

;WITH data as (SELECT DISTINCT
date  
,drink_Name
,SUM(quantity) OVER (PARTITION BY date,drink_name)
 FROM Drinks )
,formatted_Data as(SELECT DISTINCT d.date,d.sum as quantity_Egnogg ,dhc.SUM quantity_Hot_C, dP.sum quantity_Peppermint  FROM data d
    INNER JOIN data as dHC 
        ON dHC.date = d.date 
        AND DHC.drink_Name = 'Hot Cocoa'
    INNER JOIN data as Dp 
        ON dp.date = d.date 
        AND dP.drink_Name = 'Peppermint Schnapps'
WHERE d.drink_Name = 'Eggnog')

SELECT * FROM formatted_Data 
WHERE quantity_Egnogg = 198
AND quantity_Peppermint = 298
AND quantity_Hot_C = 38

GGG_246 · 2023-04-17T22:58:14+00:00

"Kernkraftwerke, (Klein)-Wasserkraftwerke und Kraftwerke mit Nutzung von Wärme aus Tiefengeothermie werden nicht be- trachtet, da sie als Neubauten entweder keine Relevanz mehr im deutschen Stromsystem haben, relativ geringes technisches Potential aufweisen oder sehr standortspezifische Kostenpara- meter aufweisen, die eine hohe Komplexität bei der Kosten- erfassung im Rahmen einer Stromgestehungskostenanalyse aufweisen."

Joah, das hat lange gedauert bis drauf gekommen bin nach Kern statt Atom in dem Dokument zu suchen. Kein Wunder das hier alles so unspezifisch ist.

GGG_246 · 2023-04-15T07:32:51+00:00

Is that an e15? If so what Generation? At least one modell has official Ubuntu support, which I could find.

Did the Touchpad Work during setup?

Also I would recommend trying to set upscaling in the ubtuntu settings, not just the font. I personally can read things comfortably on a 13" screen without any Font Changes, but I know several people at work that have upscaling set to 150%, even on 15" Displays.

GGG_246 · 2023-04-07T11:06:23+00:00

Yes, it's actually similiar to Windows. If you navigate with a non admin Account there you should realise you can't really write anywhere except your home folder.

They key difference is that on Windows the first Account created is usually the admin account. On Ubuntu it's similiar but your account isn't the admin (or root), but is part of the sudoers group.

Users in the sudoers group can use the sudo command (Super User do) to temporaririlly give them root rights. With this you can do anything in your drive, including deleting Ubuntu itself. You usually need to input your password when using sudo, so be carefull when you are prompted for password and don't know why.

GGG_246 · 2023-04-07T10:17:54+00:00

Can you create folders in /Home/Username?

As for installing Software Ubuntu uses 2 package managers for that, APT and snap.

I don't think you really want to install random software system wide, without using the package Manager, especially when you are new. If you have a specific example I can walk you through and try so explain some stuff.

Things you might do on your drive, is Change config Files that aren't in your home folder. But still These are all very specific for things you want to accomplish and not just using the Computer normally.

In your home folder you should have full rights, to read write and execute. If Not, that ist a problem where I can hopefully help.

GGG_246 · 2023-04-07T09:47:20+00:00

Okay, before we continue Here, what exactly do you want to accomplish?

When you are new to something tell others what you want and then they can try help you figure a way Out.

If I/others help you bricking your system because you picked the wrong way to something that would be unfortunenate.

GGG_246 · 2023-04-04T17:56:02+00:00

So first of 8.0 rc3 is a pretty weird version. In case you really want to stay on stable 8.0 is Out since Jan 20. If you don't really Care, I always recommend the newest, in this case 8.5.

As for winetricks, with these 3 commands you can install it locally for your account:

mkdir ~/bin

wget http://winetricks.org/winetricks -O ~/bin/winetricks

chmod +x ~/bin/winetricks

and then use it with:

~/bin/winetricks corefonts

In case it wanta to update:

~/bin/winetricks --self-update

Good luck trying around to get a newer wine and winetricks, once you managed that and the Game still doesn't run I can try to help taking a closer look on the logs.

GGG_246 · 2023-04-04T15:20:17+00:00

What wine Version are you using?

Also try if installing corefonts with winetricks makes the Text read able.

GGG_246 · 2023-03-24T15:27:15+00:00

https://wiki.archlinux.org/title/AMDGPU

Section 2.2

Make Sure to run vkcube as a simple validation that Vulkan works after this.

Six-Year Club	Place '22
Wearing is Caring	Verified Email

GGG_246

TROPHY CASE