Analyse der Testaufgabe in Tinkov [SQL] / Sudo Null IT-Nachrichten

Einführung

Kürzlich haben wir von der Tinkov-Bank einen großen Testauftrag für die Position des Datenanalysten erhalten. Es gibt viele Aufgaben, aber heute werden wir einige analysieren – wir werden auf die kleinen Dinge eingehen und auf subtile Punkte achten.

Ja, wir werden heute auch Produktmetriken berücksichtigen :)Ja, wir werden heute auch Produktmetriken berücksichtigen 🙂

Vom Team erstelltes Material Simulator „SQL für die Datenanalyse“.

Datenbankbeschreibung

Wir erhalten also die folgende Tabellenstruktur:

Aufbau von Tabellen in einer DatenbankAufbau von Tabellen in einer Datenbank

In welchem ​​DBMS wir arbeiten werden – es wird nicht gesagt. Aufgrund von Indizien gehen wir davon aus, dass es sich um PostgreSQL handelt.

Obwohl dies eigentlich nicht besonders wichtig ist – nur einige Funktionen werden sich unterscheiden. Alle Basisoperatoren sind gleich.

Übrigens eine interessante Frage: Stellen Sie sich vor, Sie könnten den Interviewer bitten, Ihnen ein Beispiel für eine beliebige Anfrage zu geben.

Welche Abfrage möchten Sie schreiben, um zu verstehen, mit welchem ​​DBMS Sie es zu tun haben?

Wir schlagen vor, die Antwort auf diese Frage in den Kommentaren zu diskutieren, und fahren mit den Aufgaben fort! 🙃

Aufgabe 1

Bedingung

Sie müssen eine Liste der Mitarbeiter im Format “Ivanova – Natalia – Yurievna” erhalten. Vollständiger Name sollte in einer Spalte geschrieben werden, Trennung -.

Ausgabe: neues Feld, nennen wir es fio, geburt_dt

Lösung

Diese Aufgabe ist ganz einfach – es ist nicht einmal notwendig, andere Tabellen zu verbinden, es reicht aus, mit der Employees-Tabelle zu arbeiten.

Das Hauptproblem besteht darin, den vollständigen Namen durch das angegebene Trennzeichen anzuzeigen. Viele lösen dieses Problem mit einer einfachen Verkettung:

wählen Sie first_nm || ‘—’ || middle_nm || ‘—’ || last_nm als fio, birth_dt von Angestellten

Aber wir arbeiten in PostgreSQL, also werden wir ein Goodie verwenden – die CONCAT_WS-Funktion. Es führt auch eine Zeichenfolgenverkettung durch, nimmt jedoch ein Trennzeichen als erstes Argument:

select concat_ws(‘—’, first_nm, middle_nm, last_nm) als fio, birth_dt von Angestellten

Sieht hübscher aus. Gleichzeitig flashten sie ihr Wissen vor dem Interviewer 😅

Aufgabe 2

Bedingung

Ausgabe %% gewählt für jeden Tag. Zeitraum vom 01.10.2020 bis zum heutigen Tag.

%% gewählt ist der Prozentsatz der empfangenen Anrufe (dozv_flg=1) aller eingehenden Anrufe (dozv_flg = 1 oder dozv_flg = 0).

Ausgabe: Datum, Sla (%% gewählt)

Lösung

Hier ist die Aufgabe schon interessanter – wir arbeiten immer noch mit einer Tabelle, aber viele Bewerber für solche Aufgaben beginnen, mehrstufige Unterabfragen einzuzäunen.

Aber eigentlich ist alles ganz einfach – man muss nur wissen, dass der CASE-Bedingungsoperator innerhalb von Aggregatfunktionen verwendet werden kann – zum Beispiel COUNT.

Um das SLA zu berechnen, benötigen wir also:

  • Anzahl der Aufrufe mit dozv_flg = 1 zählen

  • Zählen Sie die Gesamtzahl der Anrufe

  • ineinander teilen

Lassen Sie uns dies in einer Abfrage tun, ohne Unterabfragen und CTEs.

select start_dttm::date als “date”, count(case when dozv_flg=1 then 1 end) / count(case when dozv_flg in (1, 0) then 1 end) als sla von Aufrufen wo start_dttm::date between ‘2020- 10-01’ und now()::date group by start_dttm::date

Das ist eigentlich alles. Aber lassen Sie uns über einige wichtige Punkte sprechen:

Warum haben wir nicht gezählt

but count(case when dozv_flg in (1, 0) then 1 end)?

Wir sind einfach auf Nummer sicher gegangen – plötzlich kann es andere Werte geben. Zum Beispiel 2. Es wird auf keinen Fall überflüssig sein.

Warum konvertieren wir mit ::date?

Aber weil der between-Operator alle Datensätze für heute verliert, wenn diese Felder nicht in ein Datum konvertiert werden (dies ist eine Funktion des PostgreSQL-Datum-Zeit-Vergleichs). Nochmals – wir sind einfach auf Nummer sicher gegangen.

Aufgabe 3

Bedingung

Gegeben eine Clinets-Tabelle:

Sie müssen eine Abfrage schreiben, um MAU zu berechnen.

Lösung

Wenn überhaupt, MAU – Monatlich aktive Benutzer: Die Anzahl der einzelnen Clients, die während des Monats in der Anwendung aktiv waren.

Viele zeigen MAU fälschlicherweise in Form einer Tabelle mit den Spalten Monat – Anzahl aktiver Clients an. Das ist falsch – MAU sollte immer eine Zahl sein.

  • Dementsprechend reduziert sich die Lösung des Problems auf folgende Punkte:

  • Zählen Sie die Anzahl der eindeutigen Kunden für jeden Monat

Durchschnittsdaten für alle Monate

Um das Problem zu lösen, verwenden wir den CTE und den DISTINCT-Operator innerhalb des COUNT:

mit a als (select to_char(calendar_dt, ‘MM’) as mon, count(distinct id) as cnt from clients group by mon) select avg(cnt) as mau from a

Wir stellen sofort fest, dass MAU auf andere Weise betrachtet werden kann. Zum Beispiel:

Wir haben gerade eine der Möglichkeiten gezeigt 😇

Epilog

Heute werden wir uns auf diese 3 Aufgaben konzentrieren. Wir haben noch viel Interessantes auf Lager – wenn es euch also gefallen hat, melden wir uns bald mit einer neuen Analyse zurück!

Im Allgemeinen sind die Aufgaben nicht sehr schwierig. Aber wir haben versucht, einige subtile Punkte „hervorzuheben“, ohne die die Lösung dieser Probleme zu einem Problem werden kann.

Möchten Sie mehr darüber erfahren, wie man coole SQL-Abfragen schreibt, Edgehooks durchführt und Produktmetriken berechnet? Lassen Sie sich von den Jungs von Simulative im SQL-Simulator trainieren – es gibt viele coole Dinge 👍 Noch mehr interessante Materialien – in unserem TelegrammKanal

. Vom Team erstelltes Material Simulator

„SQL für die Datenanalyse“.

Similar Posts

Leave a Reply

Your email address will not be published.